sqlglot.generator

   1from __future__ import annotations
   2
   3import logging
   4import re
   5import typing as t
   6from collections import defaultdict
   7from functools import reduce, wraps
   8
   9from sqlglot import exp
  10from sqlglot.errors import ErrorLevel, UnsupportedError, concat_messages
  11from sqlglot.helper import apply_index_offset, csv, name_sequence, seq_get
  12from sqlglot.jsonpath import ALL_JSON_PATH_PARTS, JSON_PATH_PART_TRANSFORMS
  13from sqlglot.time import format_time
  14from sqlglot.tokens import TokenType
  15
  16if t.TYPE_CHECKING:
  17    from sqlglot._typing import E
  18    from sqlglot.dialects.dialect import DialectType
  19
  20    G = t.TypeVar("G", bound="Generator")
  21    GeneratorMethod = t.Callable[[G, E], str]
  22
  23logger = logging.getLogger("sqlglot")
  24
  25ESCAPED_UNICODE_RE = re.compile(r"\\(\d+)")
  26UNSUPPORTED_TEMPLATE = "Argument '{}' is not supported for expression '{}' when targeting {}."
  27
  28
  29def unsupported_args(
  30    *args: t.Union[str, t.Tuple[str, str]],
  31) -> t.Callable[[GeneratorMethod], GeneratorMethod]:
  32    """
  33    Decorator that can be used to mark certain args of an `Expression` subclass as unsupported.
  34    It expects a sequence of argument names or pairs of the form (argument_name, diagnostic_msg).
  35    """
  36    diagnostic_by_arg: t.Dict[str, t.Optional[str]] = {}
  37    for arg in args:
  38        if isinstance(arg, str):
  39            diagnostic_by_arg[arg] = None
  40        else:
  41            diagnostic_by_arg[arg[0]] = arg[1]
  42
  43    def decorator(func: GeneratorMethod) -> GeneratorMethod:
  44        @wraps(func)
  45        def _func(generator: G, expression: E) -> str:
  46            expression_name = expression.__class__.__name__
  47            dialect_name = generator.dialect.__class__.__name__
  48
  49            for arg_name, diagnostic in diagnostic_by_arg.items():
  50                if expression.args.get(arg_name):
  51                    diagnostic = diagnostic or UNSUPPORTED_TEMPLATE.format(
  52                        arg_name, expression_name, dialect_name
  53                    )
  54                    generator.unsupported(diagnostic)
  55
  56            return func(generator, expression)
  57
  58        return _func
  59
  60    return decorator
  61
  62
  63class _Generator(type):
  64    def __new__(cls, clsname, bases, attrs):
  65        klass = super().__new__(cls, clsname, bases, attrs)
  66
  67        # Remove transforms that correspond to unsupported JSONPathPart expressions
  68        for part in ALL_JSON_PATH_PARTS - klass.SUPPORTED_JSON_PATH_PARTS:
  69            klass.TRANSFORMS.pop(part, None)
  70
  71        return klass
  72
  73
  74class Generator(metaclass=_Generator):
  75    """
  76    Generator converts a given syntax tree to the corresponding SQL string.
  77
  78    Args:
  79        pretty: Whether to format the produced SQL string.
  80            Default: False.
  81        identify: Determines when an identifier should be quoted. Possible values are:
  82            False (default): Never quote, except in cases where it's mandatory by the dialect.
  83            True or 'always': Always quote.
  84            'safe': Only quote identifiers that are case insensitive.
  85        normalize: Whether to normalize identifiers to lowercase.
  86            Default: False.
  87        pad: The pad size in a formatted string. For example, this affects the indentation of
  88            a projection in a query, relative to its nesting level.
  89            Default: 2.
  90        indent: The indentation size in a formatted string. For example, this affects the
  91            indentation of subqueries and filters under a `WHERE` clause.
  92            Default: 2.
  93        normalize_functions: How to normalize function names. Possible values are:
  94            "upper" or True (default): Convert names to uppercase.
  95            "lower": Convert names to lowercase.
  96            False: Disables function name normalization.
  97        unsupported_level: Determines the generator's behavior when it encounters unsupported expressions.
  98            Default ErrorLevel.WARN.
  99        max_unsupported: Maximum number of unsupported messages to include in a raised UnsupportedError.
 100            This is only relevant if unsupported_level is ErrorLevel.RAISE.
 101            Default: 3
 102        leading_comma: Whether the comma is leading or trailing in select expressions.
 103            This is only relevant when generating in pretty mode.
 104            Default: False
 105        max_text_width: The max number of characters in a segment before creating new lines in pretty mode.
 106            The default is on the smaller end because the length only represents a segment and not the true
 107            line length.
 108            Default: 80
 109        comments: Whether to preserve comments in the output SQL code.
 110            Default: True
 111    """
 112
 113    TRANSFORMS: t.Dict[t.Type[exp.Expression], t.Callable[..., str]] = {
 114        **JSON_PATH_PART_TRANSFORMS,
 115        exp.AllowedValuesProperty: lambda self,
 116        e: f"ALLOWED_VALUES {self.expressions(e, flat=True)}",
 117        exp.AnalyzeColumns: lambda self, e: self.sql(e, "this"),
 118        exp.AnalyzeWith: lambda self, e: self.expressions(e, prefix="WITH ", sep=" "),
 119        exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
 120        exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
 121        exp.AutoRefreshProperty: lambda self, e: f"AUTO REFRESH {self.sql(e, 'this')}",
 122        exp.BackupProperty: lambda self, e: f"BACKUP {self.sql(e, 'this')}",
 123        exp.CaseSpecificColumnConstraint: lambda _,
 124        e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC",
 125        exp.Ceil: lambda self, e: self.ceil_floor(e),
 126        exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}",
 127        exp.CharacterSetProperty: lambda self,
 128        e: f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}",
 129        exp.ClusteredColumnConstraint: lambda self,
 130        e: f"CLUSTERED ({self.expressions(e, 'this', indent=False)})",
 131        exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}",
 132        exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}",
 133        exp.ConnectByRoot: lambda self, e: f"CONNECT_BY_ROOT {self.sql(e, 'this')}",
 134        exp.ConvertToCharset: lambda self, e: self.func(
 135            "CONVERT", e.this, e.args["dest"], e.args.get("source")
 136        ),
 137        exp.CopyGrantsProperty: lambda *_: "COPY GRANTS",
 138        exp.CredentialsProperty: lambda self,
 139        e: f"CREDENTIALS=({self.expressions(e, 'expressions', sep=' ')})",
 140        exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}",
 141        exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}",
 142        exp.DynamicProperty: lambda *_: "DYNAMIC",
 143        exp.EmptyProperty: lambda *_: "EMPTY",
 144        exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}",
 145        exp.EnviromentProperty: lambda self, e: f"ENVIRONMENT ({self.expressions(e, flat=True)})",
 146        exp.EphemeralColumnConstraint: lambda self,
 147        e: f"EPHEMERAL{(' ' + self.sql(e, 'this')) if e.this else ''}",
 148        exp.ExcludeColumnConstraint: lambda self, e: f"EXCLUDE {self.sql(e, 'this').lstrip()}",
 149        exp.ExecuteAsProperty: lambda self, e: self.naked_property(e),
 150        exp.Except: lambda self, e: self.set_operations(e),
 151        exp.ExternalProperty: lambda *_: "EXTERNAL",
 152        exp.Floor: lambda self, e: self.ceil_floor(e),
 153        exp.Get: lambda self, e: self.get_put_sql(e),
 154        exp.GlobalProperty: lambda *_: "GLOBAL",
 155        exp.HeapProperty: lambda *_: "HEAP",
 156        exp.IcebergProperty: lambda *_: "ICEBERG",
 157        exp.InheritsProperty: lambda self, e: f"INHERITS ({self.expressions(e, flat=True)})",
 158        exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
 159        exp.InputModelProperty: lambda self, e: f"INPUT{self.sql(e, 'this')}",
 160        exp.Intersect: lambda self, e: self.set_operations(e),
 161        exp.IntervalSpan: lambda self, e: f"{self.sql(e, 'this')} TO {self.sql(e, 'expression')}",
 162        exp.Int64: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.BIGINT)),
 163        exp.LanguageProperty: lambda self, e: self.naked_property(e),
 164        exp.LocationProperty: lambda self, e: self.naked_property(e),
 165        exp.LogProperty: lambda _, e: f"{'NO ' if e.args.get('no') else ''}LOG",
 166        exp.MaterializedProperty: lambda *_: "MATERIALIZED",
 167        exp.NonClusteredColumnConstraint: lambda self,
 168        e: f"NONCLUSTERED ({self.expressions(e, 'this', indent=False)})",
 169        exp.NoPrimaryIndexProperty: lambda *_: "NO PRIMARY INDEX",
 170        exp.NotForReplicationColumnConstraint: lambda *_: "NOT FOR REPLICATION",
 171        exp.OnCommitProperty: lambda _,
 172        e: f"ON COMMIT {'DELETE' if e.args.get('delete') else 'PRESERVE'} ROWS",
 173        exp.OnProperty: lambda self, e: f"ON {self.sql(e, 'this')}",
 174        exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}",
 175        exp.Operator: lambda self, e: self.binary(e, ""),  # The operator is produced in `binary`
 176        exp.OutputModelProperty: lambda self, e: f"OUTPUT{self.sql(e, 'this')}",
 177        exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
 178        exp.PartitionedByBucket: lambda self, e: self.func("BUCKET", e.this, e.expression),
 179        exp.PartitionByTruncate: lambda self, e: self.func("TRUNCATE", e.this, e.expression),
 180        exp.PivotAny: lambda self, e: f"ANY{self.sql(e, 'this')}",
 181        exp.PositionalColumn: lambda self, e: f"#{self.sql(e, 'this')}",
 182        exp.ProjectionPolicyColumnConstraint: lambda self,
 183        e: f"PROJECTION POLICY {self.sql(e, 'this')}",
 184        exp.Put: lambda self, e: self.get_put_sql(e),
 185        exp.RemoteWithConnectionModelProperty: lambda self,
 186        e: f"REMOTE WITH CONNECTION {self.sql(e, 'this')}",
 187        exp.ReturnsProperty: lambda self, e: (
 188            "RETURNS NULL ON NULL INPUT" if e.args.get("null") else self.naked_property(e)
 189        ),
 190        exp.SampleProperty: lambda self, e: f"SAMPLE BY {self.sql(e, 'this')}",
 191        exp.SecureProperty: lambda *_: "SECURE",
 192        exp.SecurityProperty: lambda self, e: f"SECURITY {self.sql(e, 'this')}",
 193        exp.SetConfigProperty: lambda self, e: self.sql(e, "this"),
 194        exp.SetProperty: lambda _, e: f"{'MULTI' if e.args.get('multi') else ''}SET",
 195        exp.SettingsProperty: lambda self, e: f"SETTINGS{self.seg('')}{(self.expressions(e))}",
 196        exp.SharingProperty: lambda self, e: f"SHARING={self.sql(e, 'this')}",
 197        exp.SqlReadWriteProperty: lambda _, e: e.name,
 198        exp.SqlSecurityProperty: lambda _,
 199        e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}",
 200        exp.StabilityProperty: lambda _, e: e.name,
 201        exp.Stream: lambda self, e: f"STREAM {self.sql(e, 'this')}",
 202        exp.StreamingTableProperty: lambda *_: "STREAMING",
 203        exp.StrictProperty: lambda *_: "STRICT",
 204        exp.SwapTable: lambda self, e: f"SWAP WITH {self.sql(e, 'this')}",
 205        exp.TableColumn: lambda self, e: self.sql(e.this),
 206        exp.Tags: lambda self, e: f"TAG ({self.expressions(e, flat=True)})",
 207        exp.TemporaryProperty: lambda *_: "TEMPORARY",
 208        exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
 209        exp.ToMap: lambda self, e: f"MAP {self.sql(e, 'this')}",
 210        exp.ToTableProperty: lambda self, e: f"TO {self.sql(e.this)}",
 211        exp.TransformModelProperty: lambda self, e: self.func("TRANSFORM", *e.expressions),
 212        exp.TransientProperty: lambda *_: "TRANSIENT",
 213        exp.Union: lambda self, e: self.set_operations(e),
 214        exp.UnloggedProperty: lambda *_: "UNLOGGED",
 215        exp.UsingTemplateProperty: lambda self, e: f"USING TEMPLATE {self.sql(e, 'this')}",
 216        exp.UsingData: lambda self, e: f"USING DATA {self.sql(e, 'this')}",
 217        exp.Uuid: lambda *_: "UUID()",
 218        exp.UppercaseColumnConstraint: lambda *_: "UPPERCASE",
 219        exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
 220        exp.ViewAttributeProperty: lambda self, e: f"WITH {self.sql(e, 'this')}",
 221        exp.VolatileProperty: lambda *_: "VOLATILE",
 222        exp.WeekStart: lambda self, e: f"WEEK({self.sql(e, 'this')})",
 223        exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}",
 224        exp.WithProcedureOptions: lambda self, e: f"WITH {self.expressions(e, flat=True)}",
 225        exp.WithSchemaBindingProperty: lambda self, e: f"WITH SCHEMA {self.sql(e, 'this')}",
 226        exp.WithOperator: lambda self, e: f"{self.sql(e, 'this')} WITH {self.sql(e, 'op')}",
 227        exp.ForceProperty: lambda *_: "FORCE",
 228    }
 229
 230    # Whether null ordering is supported in order by
 231    # True: Full Support, None: No support, False: No support for certain cases
 232    # such as window specifications, aggregate functions etc
 233    NULL_ORDERING_SUPPORTED: t.Optional[bool] = True
 234
 235    # Whether ignore nulls is inside the agg or outside.
 236    # FIRST(x IGNORE NULLS) OVER vs FIRST (x) IGNORE NULLS OVER
 237    IGNORE_NULLS_IN_FUNC = False
 238
 239    # Whether locking reads (i.e. SELECT ... FOR UPDATE/SHARE) are supported
 240    LOCKING_READS_SUPPORTED = False
 241
 242    # Whether the EXCEPT and INTERSECT operations can return duplicates
 243    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = True
 244
 245    # Wrap derived values in parens, usually standard but spark doesn't support it
 246    WRAP_DERIVED_VALUES = True
 247
 248    # Whether create function uses an AS before the RETURN
 249    CREATE_FUNCTION_RETURN_AS = True
 250
 251    # Whether MERGE ... WHEN MATCHED BY SOURCE is allowed
 252    MATCHED_BY_SOURCE = True
 253
 254    # Whether the INTERVAL expression works only with values like '1 day'
 255    SINGLE_STRING_INTERVAL = False
 256
 257    # Whether the plural form of date parts like day (i.e. "days") is supported in INTERVALs
 258    INTERVAL_ALLOWS_PLURAL_FORM = True
 259
 260    # Whether limit and fetch are supported (possible values: "ALL", "LIMIT", "FETCH")
 261    LIMIT_FETCH = "ALL"
 262
 263    # Whether limit and fetch allows expresions or just limits
 264    LIMIT_ONLY_LITERALS = False
 265
 266    # Whether a table is allowed to be renamed with a db
 267    RENAME_TABLE_WITH_DB = True
 268
 269    # The separator for grouping sets and rollups
 270    GROUPINGS_SEP = ","
 271
 272    # The string used for creating an index on a table
 273    INDEX_ON = "ON"
 274
 275    # Whether join hints should be generated
 276    JOIN_HINTS = True
 277
 278    # Whether table hints should be generated
 279    TABLE_HINTS = True
 280
 281    # Whether query hints should be generated
 282    QUERY_HINTS = True
 283
 284    # What kind of separator to use for query hints
 285    QUERY_HINT_SEP = ", "
 286
 287    # Whether comparing against booleans (e.g. x IS TRUE) is supported
 288    IS_BOOL_ALLOWED = True
 289
 290    # Whether to include the "SET" keyword in the "INSERT ... ON DUPLICATE KEY UPDATE" statement
 291    DUPLICATE_KEY_UPDATE_WITH_SET = True
 292
 293    # Whether to generate the limit as TOP <value> instead of LIMIT <value>
 294    LIMIT_IS_TOP = False
 295
 296    # Whether to generate INSERT INTO ... RETURNING or INSERT INTO RETURNING ...
 297    RETURNING_END = True
 298
 299    # Whether to generate an unquoted value for EXTRACT's date part argument
 300    EXTRACT_ALLOWS_QUOTES = True
 301
 302    # Whether TIMETZ / TIMESTAMPTZ will be generated using the "WITH TIME ZONE" syntax
 303    TZ_TO_WITH_TIME_ZONE = False
 304
 305    # Whether the NVL2 function is supported
 306    NVL2_SUPPORTED = True
 307
 308    # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
 309    SELECT_KINDS: t.Tuple[str, ...] = ("STRUCT", "VALUE")
 310
 311    # Whether VALUES statements can be used as derived tables.
 312    # MySQL 5 and Redshift do not allow this, so when False, it will convert
 313    # SELECT * VALUES into SELECT UNION
 314    VALUES_AS_TABLE = True
 315
 316    # Whether the word COLUMN is included when adding a column with ALTER TABLE
 317    ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = True
 318
 319    # UNNEST WITH ORDINALITY (presto) instead of UNNEST WITH OFFSET (bigquery)
 320    UNNEST_WITH_ORDINALITY = True
 321
 322    # Whether FILTER (WHERE cond) can be used for conditional aggregation
 323    AGGREGATE_FILTER_SUPPORTED = True
 324
 325    # Whether JOIN sides (LEFT, RIGHT) are supported in conjunction with SEMI/ANTI join kinds
 326    SEMI_ANTI_JOIN_WITH_SIDE = True
 327
 328    # Whether to include the type of a computed column in the CREATE DDL
 329    COMPUTED_COLUMN_WITH_TYPE = True
 330
 331    # Whether CREATE TABLE .. COPY .. is supported. False means we'll generate CLONE instead of COPY
 332    SUPPORTS_TABLE_COPY = True
 333
 334    # Whether parentheses are required around the table sample's expression
 335    TABLESAMPLE_REQUIRES_PARENS = True
 336
 337    # Whether a table sample clause's size needs to be followed by the ROWS keyword
 338    TABLESAMPLE_SIZE_IS_ROWS = True
 339
 340    # The keyword(s) to use when generating a sample clause
 341    TABLESAMPLE_KEYWORDS = "TABLESAMPLE"
 342
 343    # Whether the TABLESAMPLE clause supports a method name, like BERNOULLI
 344    TABLESAMPLE_WITH_METHOD = True
 345
 346    # The keyword to use when specifying the seed of a sample clause
 347    TABLESAMPLE_SEED_KEYWORD = "SEED"
 348
 349    # Whether COLLATE is a function instead of a binary operator
 350    COLLATE_IS_FUNC = False
 351
 352    # Whether data types support additional specifiers like e.g. CHAR or BYTE (oracle)
 353    DATA_TYPE_SPECIFIERS_ALLOWED = False
 354
 355    # Whether conditions require booleans WHERE x = 0 vs WHERE x
 356    ENSURE_BOOLS = False
 357
 358    # Whether the "RECURSIVE" keyword is required when defining recursive CTEs
 359    CTE_RECURSIVE_KEYWORD_REQUIRED = True
 360
 361    # Whether CONCAT requires >1 arguments
 362    SUPPORTS_SINGLE_ARG_CONCAT = True
 363
 364    # Whether LAST_DAY function supports a date part argument
 365    LAST_DAY_SUPPORTS_DATE_PART = True
 366
 367    # Whether named columns are allowed in table aliases
 368    SUPPORTS_TABLE_ALIAS_COLUMNS = True
 369
 370    # Whether UNPIVOT aliases are Identifiers (False means they're Literals)
 371    UNPIVOT_ALIASES_ARE_IDENTIFIERS = True
 372
 373    # What delimiter to use for separating JSON key/value pairs
 374    JSON_KEY_VALUE_PAIR_SEP = ":"
 375
 376    # INSERT OVERWRITE TABLE x override
 377    INSERT_OVERWRITE = " OVERWRITE TABLE"
 378
 379    # Whether the SELECT .. INTO syntax is used instead of CTAS
 380    SUPPORTS_SELECT_INTO = False
 381
 382    # Whether UNLOGGED tables can be created
 383    SUPPORTS_UNLOGGED_TABLES = False
 384
 385    # Whether the CREATE TABLE LIKE statement is supported
 386    SUPPORTS_CREATE_TABLE_LIKE = True
 387
 388    # Whether the LikeProperty needs to be specified inside of the schema clause
 389    LIKE_PROPERTY_INSIDE_SCHEMA = False
 390
 391    # Whether DISTINCT can be followed by multiple args in an AggFunc. If not, it will be
 392    # transpiled into a series of CASE-WHEN-ELSE, ultimately using a tuple conseisting of the args
 393    MULTI_ARG_DISTINCT = True
 394
 395    # Whether the JSON extraction operators expect a value of type JSON
 396    JSON_TYPE_REQUIRED_FOR_EXTRACTION = False
 397
 398    # Whether bracketed keys like ["foo"] are supported in JSON paths
 399    JSON_PATH_BRACKETED_KEY_SUPPORTED = True
 400
 401    # Whether to escape keys using single quotes in JSON paths
 402    JSON_PATH_SINGLE_QUOTE_ESCAPE = False
 403
 404    # The JSONPathPart expressions supported by this dialect
 405    SUPPORTED_JSON_PATH_PARTS = ALL_JSON_PATH_PARTS.copy()
 406
 407    # Whether any(f(x) for x in array) can be implemented by this dialect
 408    CAN_IMPLEMENT_ARRAY_ANY = False
 409
 410    # Whether the function TO_NUMBER is supported
 411    SUPPORTS_TO_NUMBER = True
 412
 413    # Whether EXCLUDE in window specification is supported
 414    SUPPORTS_WINDOW_EXCLUDE = False
 415
 416    # Whether or not set op modifiers apply to the outer set op or select.
 417    # SELECT * FROM x UNION SELECT * FROM y LIMIT 1
 418    # True means limit 1 happens after the set op, False means it it happens on y.
 419    SET_OP_MODIFIERS = True
 420
 421    # Whether parameters from COPY statement are wrapped in parentheses
 422    COPY_PARAMS_ARE_WRAPPED = True
 423
 424    # Whether values of params are set with "=" token or empty space
 425    COPY_PARAMS_EQ_REQUIRED = False
 426
 427    # Whether COPY statement has INTO keyword
 428    COPY_HAS_INTO_KEYWORD = True
 429
 430    # Whether the conditional TRY(expression) function is supported
 431    TRY_SUPPORTED = True
 432
 433    # Whether the UESCAPE syntax in unicode strings is supported
 434    SUPPORTS_UESCAPE = True
 435
 436    # The keyword to use when generating a star projection with excluded columns
 437    STAR_EXCEPT = "EXCEPT"
 438
 439    # The HEX function name
 440    HEX_FUNC = "HEX"
 441
 442    # The keywords to use when prefixing & separating WITH based properties
 443    WITH_PROPERTIES_PREFIX = "WITH"
 444
 445    # Whether to quote the generated expression of exp.JsonPath
 446    QUOTE_JSON_PATH = True
 447
 448    # Whether the text pattern/fill (3rd) parameter of RPAD()/LPAD() is optional (defaults to space)
 449    PAD_FILL_PATTERN_IS_REQUIRED = False
 450
 451    # Whether a projection can explode into multiple rows, e.g. by unnesting an array.
 452    SUPPORTS_EXPLODING_PROJECTIONS = True
 453
 454    # Whether ARRAY_CONCAT can be generated with varlen args or if it should be reduced to 2-arg version
 455    ARRAY_CONCAT_IS_VAR_LEN = True
 456
 457    # Whether CONVERT_TIMEZONE() is supported; if not, it will be generated as exp.AtTimeZone
 458    SUPPORTS_CONVERT_TIMEZONE = False
 459
 460    # Whether MEDIAN(expr) is supported; if not, it will be generated as PERCENTILE_CONT(expr, 0.5)
 461    SUPPORTS_MEDIAN = True
 462
 463    # Whether UNIX_SECONDS(timestamp) is supported
 464    SUPPORTS_UNIX_SECONDS = False
 465
 466    # Whether to wrap <props> in `AlterSet`, e.g., ALTER ... SET (<props>)
 467    ALTER_SET_WRAPPED = False
 468
 469    # Whether to normalize the date parts in EXTRACT(<date_part> FROM <expr>) into a common representation
 470    # For instance, to extract the day of week in ISO semantics, one can use ISODOW, DAYOFWEEKISO etc depending on the dialect.
 471    # TODO: The normalization should be done by default once we've tested it across all dialects.
 472    NORMALIZE_EXTRACT_DATE_PARTS = False
 473
 474    # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
 475    PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
 476
 477    # The function name of the exp.ArraySize expression
 478    ARRAY_SIZE_NAME: str = "ARRAY_LENGTH"
 479
 480    # The syntax to use when altering the type of a column
 481    ALTER_SET_TYPE = "SET DATA TYPE"
 482
 483    # Whether exp.ArraySize should generate the dimension arg too (valid for Postgres & DuckDB)
 484    # None -> Doesn't support it at all
 485    # False (DuckDB) -> Has backwards-compatible support, but preferably generated without
 486    # True (Postgres) -> Explicitly requires it
 487    ARRAY_SIZE_DIM_REQUIRED: t.Optional[bool] = None
 488
 489    # Whether a multi-argument DECODE(...) function is supported. If not, a CASE expression is generated
 490    SUPPORTS_DECODE_CASE = True
 491
 492    # Whether SYMMETRIC and ASYMMETRIC flags are supported with BETWEEN expression
 493    SUPPORTS_BETWEEN_FLAGS = False
 494
 495    # Whether LIKE and ILIKE support quantifiers such as LIKE ANY/ALL/SOME
 496    SUPPORTS_LIKE_QUANTIFIERS = True
 497
 498    TYPE_MAPPING = {
 499        exp.DataType.Type.DATETIME2: "TIMESTAMP",
 500        exp.DataType.Type.NCHAR: "CHAR",
 501        exp.DataType.Type.NVARCHAR: "VARCHAR",
 502        exp.DataType.Type.MEDIUMTEXT: "TEXT",
 503        exp.DataType.Type.LONGTEXT: "TEXT",
 504        exp.DataType.Type.TINYTEXT: "TEXT",
 505        exp.DataType.Type.BLOB: "VARBINARY",
 506        exp.DataType.Type.MEDIUMBLOB: "BLOB",
 507        exp.DataType.Type.LONGBLOB: "BLOB",
 508        exp.DataType.Type.TINYBLOB: "BLOB",
 509        exp.DataType.Type.INET: "INET",
 510        exp.DataType.Type.ROWVERSION: "VARBINARY",
 511        exp.DataType.Type.SMALLDATETIME: "TIMESTAMP",
 512    }
 513
 514    TIME_PART_SINGULARS = {
 515        "MICROSECONDS": "MICROSECOND",
 516        "SECONDS": "SECOND",
 517        "MINUTES": "MINUTE",
 518        "HOURS": "HOUR",
 519        "DAYS": "DAY",
 520        "WEEKS": "WEEK",
 521        "MONTHS": "MONTH",
 522        "QUARTERS": "QUARTER",
 523        "YEARS": "YEAR",
 524    }
 525
 526    AFTER_HAVING_MODIFIER_TRANSFORMS = {
 527        "cluster": lambda self, e: self.sql(e, "cluster"),
 528        "distribute": lambda self, e: self.sql(e, "distribute"),
 529        "sort": lambda self, e: self.sql(e, "sort"),
 530        "windows": lambda self, e: (
 531            self.seg("WINDOW ") + self.expressions(e, key="windows", flat=True)
 532            if e.args.get("windows")
 533            else ""
 534        ),
 535        "qualify": lambda self, e: self.sql(e, "qualify"),
 536    }
 537
 538    TOKEN_MAPPING: t.Dict[TokenType, str] = {}
 539
 540    STRUCT_DELIMITER = ("<", ">")
 541
 542    PARAMETER_TOKEN = "@"
 543    NAMED_PLACEHOLDER_TOKEN = ":"
 544
 545    EXPRESSION_PRECEDES_PROPERTIES_CREATABLES: t.Set[str] = set()
 546
 547    PROPERTIES_LOCATION = {
 548        exp.AllowedValuesProperty: exp.Properties.Location.POST_SCHEMA,
 549        exp.AlgorithmProperty: exp.Properties.Location.POST_CREATE,
 550        exp.AutoIncrementProperty: exp.Properties.Location.POST_SCHEMA,
 551        exp.AutoRefreshProperty: exp.Properties.Location.POST_SCHEMA,
 552        exp.BackupProperty: exp.Properties.Location.POST_SCHEMA,
 553        exp.BlockCompressionProperty: exp.Properties.Location.POST_NAME,
 554        exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA,
 555        exp.ChecksumProperty: exp.Properties.Location.POST_NAME,
 556        exp.CollateProperty: exp.Properties.Location.POST_SCHEMA,
 557        exp.CopyGrantsProperty: exp.Properties.Location.POST_SCHEMA,
 558        exp.Cluster: exp.Properties.Location.POST_SCHEMA,
 559        exp.ClusteredByProperty: exp.Properties.Location.POST_SCHEMA,
 560        exp.DistributedByProperty: exp.Properties.Location.POST_SCHEMA,
 561        exp.DuplicateKeyProperty: exp.Properties.Location.POST_SCHEMA,
 562        exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME,
 563        exp.DataDeletionProperty: exp.Properties.Location.POST_SCHEMA,
 564        exp.DefinerProperty: exp.Properties.Location.POST_CREATE,
 565        exp.DictRange: exp.Properties.Location.POST_SCHEMA,
 566        exp.DictProperty: exp.Properties.Location.POST_SCHEMA,
 567        exp.DynamicProperty: exp.Properties.Location.POST_CREATE,
 568        exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA,
 569        exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA,
 570        exp.EmptyProperty: exp.Properties.Location.POST_SCHEMA,
 571        exp.EncodeProperty: exp.Properties.Location.POST_EXPRESSION,
 572        exp.EngineProperty: exp.Properties.Location.POST_SCHEMA,
 573        exp.EnviromentProperty: exp.Properties.Location.POST_SCHEMA,
 574        exp.ExecuteAsProperty: exp.Properties.Location.POST_SCHEMA,
 575        exp.ExternalProperty: exp.Properties.Location.POST_CREATE,
 576        exp.FallbackProperty: exp.Properties.Location.POST_NAME,
 577        exp.FileFormatProperty: exp.Properties.Location.POST_WITH,
 578        exp.FreespaceProperty: exp.Properties.Location.POST_NAME,
 579        exp.GlobalProperty: exp.Properties.Location.POST_CREATE,
 580        exp.HeapProperty: exp.Properties.Location.POST_WITH,
 581        exp.InheritsProperty: exp.Properties.Location.POST_SCHEMA,
 582        exp.IcebergProperty: exp.Properties.Location.POST_CREATE,
 583        exp.IncludeProperty: exp.Properties.Location.POST_SCHEMA,
 584        exp.InputModelProperty: exp.Properties.Location.POST_SCHEMA,
 585        exp.IsolatedLoadingProperty: exp.Properties.Location.POST_NAME,
 586        exp.JournalProperty: exp.Properties.Location.POST_NAME,
 587        exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA,
 588        exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
 589        exp.LocationProperty: exp.Properties.Location.POST_SCHEMA,
 590        exp.LockProperty: exp.Properties.Location.POST_SCHEMA,
 591        exp.LockingProperty: exp.Properties.Location.POST_ALIAS,
 592        exp.LogProperty: exp.Properties.Location.POST_NAME,
 593        exp.MaterializedProperty: exp.Properties.Location.POST_CREATE,
 594        exp.MergeBlockRatioProperty: exp.Properties.Location.POST_NAME,
 595        exp.NoPrimaryIndexProperty: exp.Properties.Location.POST_EXPRESSION,
 596        exp.OnProperty: exp.Properties.Location.POST_SCHEMA,
 597        exp.OnCommitProperty: exp.Properties.Location.POST_EXPRESSION,
 598        exp.Order: exp.Properties.Location.POST_SCHEMA,
 599        exp.OutputModelProperty: exp.Properties.Location.POST_SCHEMA,
 600        exp.PartitionedByProperty: exp.Properties.Location.POST_WITH,
 601        exp.PartitionedOfProperty: exp.Properties.Location.POST_SCHEMA,
 602        exp.PrimaryKey: exp.Properties.Location.POST_SCHEMA,
 603        exp.Property: exp.Properties.Location.POST_WITH,
 604        exp.RemoteWithConnectionModelProperty: exp.Properties.Location.POST_SCHEMA,
 605        exp.ReturnsProperty: exp.Properties.Location.POST_SCHEMA,
 606        exp.RowFormatProperty: exp.Properties.Location.POST_SCHEMA,
 607        exp.RowFormatDelimitedProperty: exp.Properties.Location.POST_SCHEMA,
 608        exp.RowFormatSerdeProperty: exp.Properties.Location.POST_SCHEMA,
 609        exp.SampleProperty: exp.Properties.Location.POST_SCHEMA,
 610        exp.SchemaCommentProperty: exp.Properties.Location.POST_SCHEMA,
 611        exp.SecureProperty: exp.Properties.Location.POST_CREATE,
 612        exp.SecurityProperty: exp.Properties.Location.POST_SCHEMA,
 613        exp.SerdeProperties: exp.Properties.Location.POST_SCHEMA,
 614        exp.Set: exp.Properties.Location.POST_SCHEMA,
 615        exp.SettingsProperty: exp.Properties.Location.POST_SCHEMA,
 616        exp.SetProperty: exp.Properties.Location.POST_CREATE,
 617        exp.SetConfigProperty: exp.Properties.Location.POST_SCHEMA,
 618        exp.SharingProperty: exp.Properties.Location.POST_EXPRESSION,
 619        exp.SequenceProperties: exp.Properties.Location.POST_EXPRESSION,
 620        exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA,
 621        exp.SqlReadWriteProperty: exp.Properties.Location.POST_SCHEMA,
 622        exp.SqlSecurityProperty: exp.Properties.Location.POST_CREATE,
 623        exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA,
 624        exp.StorageHandlerProperty: exp.Properties.Location.POST_SCHEMA,
 625        exp.StreamingTableProperty: exp.Properties.Location.POST_CREATE,
 626        exp.StrictProperty: exp.Properties.Location.POST_SCHEMA,
 627        exp.Tags: exp.Properties.Location.POST_WITH,
 628        exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
 629        exp.ToTableProperty: exp.Properties.Location.POST_SCHEMA,
 630        exp.TransientProperty: exp.Properties.Location.POST_CREATE,
 631        exp.TransformModelProperty: exp.Properties.Location.POST_SCHEMA,
 632        exp.MergeTreeTTL: exp.Properties.Location.POST_SCHEMA,
 633        exp.UnloggedProperty: exp.Properties.Location.POST_CREATE,
 634        exp.UsingTemplateProperty: exp.Properties.Location.POST_SCHEMA,
 635        exp.ViewAttributeProperty: exp.Properties.Location.POST_SCHEMA,
 636        exp.VolatileProperty: exp.Properties.Location.POST_CREATE,
 637        exp.WithDataProperty: exp.Properties.Location.POST_EXPRESSION,
 638        exp.WithJournalTableProperty: exp.Properties.Location.POST_NAME,
 639        exp.WithProcedureOptions: exp.Properties.Location.POST_SCHEMA,
 640        exp.WithSchemaBindingProperty: exp.Properties.Location.POST_SCHEMA,
 641        exp.WithSystemVersioningProperty: exp.Properties.Location.POST_SCHEMA,
 642        exp.ForceProperty: exp.Properties.Location.POST_CREATE,
 643    }
 644
 645    # Keywords that can't be used as unquoted identifier names
 646    RESERVED_KEYWORDS: t.Set[str] = set()
 647
 648    # Expressions whose comments are separated from them for better formatting
 649    WITH_SEPARATED_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
 650        exp.Command,
 651        exp.Create,
 652        exp.Describe,
 653        exp.Delete,
 654        exp.Drop,
 655        exp.From,
 656        exp.Insert,
 657        exp.Join,
 658        exp.MultitableInserts,
 659        exp.Order,
 660        exp.Group,
 661        exp.Having,
 662        exp.Select,
 663        exp.SetOperation,
 664        exp.Update,
 665        exp.Where,
 666        exp.With,
 667    )
 668
 669    # Expressions that should not have their comments generated in maybe_comment
 670    EXCLUDE_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
 671        exp.Binary,
 672        exp.SetOperation,
 673    )
 674
 675    # Expressions that can remain unwrapped when appearing in the context of an INTERVAL
 676    UNWRAPPED_INTERVAL_VALUES: t.Tuple[t.Type[exp.Expression], ...] = (
 677        exp.Column,
 678        exp.Literal,
 679        exp.Neg,
 680        exp.Paren,
 681    )
 682
 683    PARAMETERIZABLE_TEXT_TYPES = {
 684        exp.DataType.Type.NVARCHAR,
 685        exp.DataType.Type.VARCHAR,
 686        exp.DataType.Type.CHAR,
 687        exp.DataType.Type.NCHAR,
 688    }
 689
 690    # Expressions that need to have all CTEs under them bubbled up to them
 691    EXPRESSIONS_WITHOUT_NESTED_CTES: t.Set[t.Type[exp.Expression]] = set()
 692
 693    RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS: t.Tuple[t.Type[exp.Expression], ...] = ()
 694
 695    SAFE_JSON_PATH_KEY_RE = exp.SAFE_IDENTIFIER_RE
 696
 697    SENTINEL_LINE_BREAK = "__SQLGLOT__LB__"
 698
 699    __slots__ = (
 700        "pretty",
 701        "identify",
 702        "normalize",
 703        "pad",
 704        "_indent",
 705        "normalize_functions",
 706        "unsupported_level",
 707        "max_unsupported",
 708        "leading_comma",
 709        "max_text_width",
 710        "comments",
 711        "dialect",
 712        "unsupported_messages",
 713        "_escaped_quote_end",
 714        "_escaped_identifier_end",
 715        "_next_name",
 716        "_identifier_start",
 717        "_identifier_end",
 718        "_quote_json_path_key_using_brackets",
 719    )
 720
 721    def __init__(
 722        self,
 723        pretty: t.Optional[bool] = None,
 724        identify: str | bool = False,
 725        normalize: bool = False,
 726        pad: int = 2,
 727        indent: int = 2,
 728        normalize_functions: t.Optional[str | bool] = None,
 729        unsupported_level: ErrorLevel = ErrorLevel.WARN,
 730        max_unsupported: int = 3,
 731        leading_comma: bool = False,
 732        max_text_width: int = 80,
 733        comments: bool = True,
 734        dialect: DialectType = None,
 735    ):
 736        import sqlglot
 737        from sqlglot.dialects import Dialect
 738
 739        self.pretty = pretty if pretty is not None else sqlglot.pretty
 740        self.identify = identify
 741        self.normalize = normalize
 742        self.pad = pad
 743        self._indent = indent
 744        self.unsupported_level = unsupported_level
 745        self.max_unsupported = max_unsupported
 746        self.leading_comma = leading_comma
 747        self.max_text_width = max_text_width
 748        self.comments = comments
 749        self.dialect = Dialect.get_or_raise(dialect)
 750
 751        # This is both a Dialect property and a Generator argument, so we prioritize the latter
 752        self.normalize_functions = (
 753            self.dialect.NORMALIZE_FUNCTIONS if normalize_functions is None else normalize_functions
 754        )
 755
 756        self.unsupported_messages: t.List[str] = []
 757        self._escaped_quote_end: str = (
 758            self.dialect.tokenizer_class.STRING_ESCAPES[0] + self.dialect.QUOTE_END
 759        )
 760        self._escaped_identifier_end = self.dialect.IDENTIFIER_END * 2
 761
 762        self._next_name = name_sequence("_t")
 763
 764        self._identifier_start = self.dialect.IDENTIFIER_START
 765        self._identifier_end = self.dialect.IDENTIFIER_END
 766
 767        self._quote_json_path_key_using_brackets = True
 768
 769    def generate(self, expression: exp.Expression, copy: bool = True) -> str:
 770        """
 771        Generates the SQL string corresponding to the given syntax tree.
 772
 773        Args:
 774            expression: The syntax tree.
 775            copy: Whether to copy the expression. The generator performs mutations so
 776                it is safer to copy.
 777
 778        Returns:
 779            The SQL string corresponding to `expression`.
 780        """
 781        if copy:
 782            expression = expression.copy()
 783
 784        expression = self.preprocess(expression)
 785
 786        self.unsupported_messages = []
 787        sql = self.sql(expression).strip()
 788
 789        if self.pretty:
 790            sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n")
 791
 792        if self.unsupported_level == ErrorLevel.IGNORE:
 793            return sql
 794
 795        if self.unsupported_level == ErrorLevel.WARN:
 796            for msg in self.unsupported_messages:
 797                logger.warning(msg)
 798        elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages:
 799            raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported))
 800
 801        return sql
 802
 803    def preprocess(self, expression: exp.Expression) -> exp.Expression:
 804        """Apply generic preprocessing transformations to a given expression."""
 805        expression = self._move_ctes_to_top_level(expression)
 806
 807        if self.ENSURE_BOOLS:
 808            from sqlglot.transforms import ensure_bools
 809
 810            expression = ensure_bools(expression)
 811
 812        return expression
 813
 814    def _move_ctes_to_top_level(self, expression: E) -> E:
 815        if (
 816            not expression.parent
 817            and type(expression) in self.EXPRESSIONS_WITHOUT_NESTED_CTES
 818            and any(node.parent is not expression for node in expression.find_all(exp.With))
 819        ):
 820            from sqlglot.transforms import move_ctes_to_top_level
 821
 822            expression = move_ctes_to_top_level(expression)
 823        return expression
 824
 825    def unsupported(self, message: str) -> None:
 826        if self.unsupported_level == ErrorLevel.IMMEDIATE:
 827            raise UnsupportedError(message)
 828        self.unsupported_messages.append(message)
 829
 830    def sep(self, sep: str = " ") -> str:
 831        return f"{sep.strip()}\n" if self.pretty else sep
 832
 833    def seg(self, sql: str, sep: str = " ") -> str:
 834        return f"{self.sep(sep)}{sql}"
 835
 836    def sanitize_comment(self, comment: str) -> str:
 837        comment = " " + comment if comment[0].strip() else comment
 838        comment = comment + " " if comment[-1].strip() else comment
 839
 840        if not self.dialect.tokenizer_class.NESTED_COMMENTS:
 841            # Necessary workaround to avoid syntax errors due to nesting: /* ... */ ... */
 842            comment = comment.replace("*/", "* /")
 843
 844        return comment
 845
 846    def maybe_comment(
 847        self,
 848        sql: str,
 849        expression: t.Optional[exp.Expression] = None,
 850        comments: t.Optional[t.List[str]] = None,
 851        separated: bool = False,
 852    ) -> str:
 853        comments = (
 854            ((expression and expression.comments) if comments is None else comments)  # type: ignore
 855            if self.comments
 856            else None
 857        )
 858
 859        if not comments or isinstance(expression, self.EXCLUDE_COMMENTS):
 860            return sql
 861
 862        comments_sql = " ".join(
 863            f"/*{self.sanitize_comment(comment)}*/" for comment in comments if comment
 864        )
 865
 866        if not comments_sql:
 867            return sql
 868
 869        comments_sql = self._replace_line_breaks(comments_sql)
 870
 871        if separated or isinstance(expression, self.WITH_SEPARATED_COMMENTS):
 872            return (
 873                f"{self.sep()}{comments_sql}{sql}"
 874                if not sql or sql[0].isspace()
 875                else f"{comments_sql}{self.sep()}{sql}"
 876            )
 877
 878        return f"{sql} {comments_sql}"
 879
 880    def wrap(self, expression: exp.Expression | str) -> str:
 881        this_sql = (
 882            self.sql(expression)
 883            if isinstance(expression, exp.UNWRAPPED_QUERIES)
 884            else self.sql(expression, "this")
 885        )
 886        if not this_sql:
 887            return "()"
 888
 889        this_sql = self.indent(this_sql, level=1, pad=0)
 890        return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}"
 891
 892    def no_identify(self, func: t.Callable[..., str], *args, **kwargs) -> str:
 893        original = self.identify
 894        self.identify = False
 895        result = func(*args, **kwargs)
 896        self.identify = original
 897        return result
 898
 899    def normalize_func(self, name: str) -> str:
 900        if self.normalize_functions == "upper" or self.normalize_functions is True:
 901            return name.upper()
 902        if self.normalize_functions == "lower":
 903            return name.lower()
 904        return name
 905
 906    def indent(
 907        self,
 908        sql: str,
 909        level: int = 0,
 910        pad: t.Optional[int] = None,
 911        skip_first: bool = False,
 912        skip_last: bool = False,
 913    ) -> str:
 914        if not self.pretty or not sql:
 915            return sql
 916
 917        pad = self.pad if pad is None else pad
 918        lines = sql.split("\n")
 919
 920        return "\n".join(
 921            (
 922                line
 923                if (skip_first and i == 0) or (skip_last and i == len(lines) - 1)
 924                else f"{' ' * (level * self._indent + pad)}{line}"
 925            )
 926            for i, line in enumerate(lines)
 927        )
 928
 929    def sql(
 930        self,
 931        expression: t.Optional[str | exp.Expression],
 932        key: t.Optional[str] = None,
 933        comment: bool = True,
 934    ) -> str:
 935        if not expression:
 936            return ""
 937
 938        if isinstance(expression, str):
 939            return expression
 940
 941        if key:
 942            value = expression.args.get(key)
 943            if value:
 944                return self.sql(value)
 945            return ""
 946
 947        transform = self.TRANSFORMS.get(expression.__class__)
 948
 949        if callable(transform):
 950            sql = transform(self, expression)
 951        elif isinstance(expression, exp.Expression):
 952            exp_handler_name = f"{expression.key}_sql"
 953
 954            if hasattr(self, exp_handler_name):
 955                sql = getattr(self, exp_handler_name)(expression)
 956            elif isinstance(expression, exp.Func):
 957                sql = self.function_fallback_sql(expression)
 958            elif isinstance(expression, exp.Property):
 959                sql = self.property_sql(expression)
 960            else:
 961                raise ValueError(f"Unsupported expression type {expression.__class__.__name__}")
 962        else:
 963            raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}")
 964
 965        return self.maybe_comment(sql, expression) if self.comments and comment else sql
 966
 967    def uncache_sql(self, expression: exp.Uncache) -> str:
 968        table = self.sql(expression, "this")
 969        exists_sql = " IF EXISTS" if expression.args.get("exists") else ""
 970        return f"UNCACHE TABLE{exists_sql} {table}"
 971
 972    def cache_sql(self, expression: exp.Cache) -> str:
 973        lazy = " LAZY" if expression.args.get("lazy") else ""
 974        table = self.sql(expression, "this")
 975        options = expression.args.get("options")
 976        options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else ""
 977        sql = self.sql(expression, "expression")
 978        sql = f" AS{self.sep()}{sql}" if sql else ""
 979        sql = f"CACHE{lazy} TABLE {table}{options}{sql}"
 980        return self.prepend_ctes(expression, sql)
 981
 982    def characterset_sql(self, expression: exp.CharacterSet) -> str:
 983        if isinstance(expression.parent, exp.Cast):
 984            return f"CHAR CHARACTER SET {self.sql(expression, 'this')}"
 985        default = "DEFAULT " if expression.args.get("default") else ""
 986        return f"{default}CHARACTER SET={self.sql(expression, 'this')}"
 987
 988    def column_parts(self, expression: exp.Column) -> str:
 989        return ".".join(
 990            self.sql(part)
 991            for part in (
 992                expression.args.get("catalog"),
 993                expression.args.get("db"),
 994                expression.args.get("table"),
 995                expression.args.get("this"),
 996            )
 997            if part
 998        )
 999
1000    def column_sql(self, expression: exp.Column) -> str:
1001        join_mark = " (+)" if expression.args.get("join_mark") else ""
1002
1003        if join_mark and not self.dialect.SUPPORTS_COLUMN_JOIN_MARKS:
1004            join_mark = ""
1005            self.unsupported("Outer join syntax using the (+) operator is not supported.")
1006
1007        return f"{self.column_parts(expression)}{join_mark}"
1008
1009    def columnposition_sql(self, expression: exp.ColumnPosition) -> str:
1010        this = self.sql(expression, "this")
1011        this = f" {this}" if this else ""
1012        position = self.sql(expression, "position")
1013        return f"{position}{this}"
1014
1015    def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
1016        column = self.sql(expression, "this")
1017        kind = self.sql(expression, "kind")
1018        constraints = self.expressions(expression, key="constraints", sep=" ", flat=True)
1019        exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
1020        kind = f"{sep}{kind}" if kind else ""
1021        constraints = f" {constraints}" if constraints else ""
1022        position = self.sql(expression, "position")
1023        position = f" {position}" if position else ""
1024
1025        if expression.find(exp.ComputedColumnConstraint) and not self.COMPUTED_COLUMN_WITH_TYPE:
1026            kind = ""
1027
1028        return f"{exists}{column}{kind}{constraints}{position}"
1029
1030    def columnconstraint_sql(self, expression: exp.ColumnConstraint) -> str:
1031        this = self.sql(expression, "this")
1032        kind_sql = self.sql(expression, "kind").strip()
1033        return f"CONSTRAINT {this} {kind_sql}" if this else kind_sql
1034
1035    def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
1036        this = self.sql(expression, "this")
1037        if expression.args.get("not_null"):
1038            persisted = " PERSISTED NOT NULL"
1039        elif expression.args.get("persisted"):
1040            persisted = " PERSISTED"
1041        else:
1042            persisted = ""
1043
1044        return f"AS {this}{persisted}"
1045
1046    def autoincrementcolumnconstraint_sql(self, _) -> str:
1047        return self.token_sql(TokenType.AUTO_INCREMENT)
1048
1049    def compresscolumnconstraint_sql(self, expression: exp.CompressColumnConstraint) -> str:
1050        if isinstance(expression.this, list):
1051            this = self.wrap(self.expressions(expression, key="this", flat=True))
1052        else:
1053            this = self.sql(expression, "this")
1054
1055        return f"COMPRESS {this}"
1056
1057    def generatedasidentitycolumnconstraint_sql(
1058        self, expression: exp.GeneratedAsIdentityColumnConstraint
1059    ) -> str:
1060        this = ""
1061        if expression.this is not None:
1062            on_null = " ON NULL" if expression.args.get("on_null") else ""
1063            this = " ALWAYS" if expression.this else f" BY DEFAULT{on_null}"
1064
1065        start = expression.args.get("start")
1066        start = f"START WITH {start}" if start else ""
1067        increment = expression.args.get("increment")
1068        increment = f" INCREMENT BY {increment}" if increment else ""
1069        minvalue = expression.args.get("minvalue")
1070        minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1071        maxvalue = expression.args.get("maxvalue")
1072        maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1073        cycle = expression.args.get("cycle")
1074        cycle_sql = ""
1075
1076        if cycle is not None:
1077            cycle_sql = f"{' NO' if not cycle else ''} CYCLE"
1078            cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql
1079
1080        sequence_opts = ""
1081        if start or increment or cycle_sql:
1082            sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}"
1083            sequence_opts = f" ({sequence_opts.strip()})"
1084
1085        expr = self.sql(expression, "expression")
1086        expr = f"({expr})" if expr else "IDENTITY"
1087
1088        return f"GENERATED{this} AS {expr}{sequence_opts}"
1089
1090    def generatedasrowcolumnconstraint_sql(
1091        self, expression: exp.GeneratedAsRowColumnConstraint
1092    ) -> str:
1093        start = "START" if expression.args.get("start") else "END"
1094        hidden = " HIDDEN" if expression.args.get("hidden") else ""
1095        return f"GENERATED ALWAYS AS ROW {start}{hidden}"
1096
1097    def periodforsystemtimeconstraint_sql(
1098        self, expression: exp.PeriodForSystemTimeConstraint
1099    ) -> str:
1100        return f"PERIOD FOR SYSTEM_TIME ({self.sql(expression, 'this')}, {self.sql(expression, 'expression')})"
1101
1102    def notnullcolumnconstraint_sql(self, expression: exp.NotNullColumnConstraint) -> str:
1103        return f"{'' if expression.args.get('allow_null') else 'NOT '}NULL"
1104
1105    def primarykeycolumnconstraint_sql(self, expression: exp.PrimaryKeyColumnConstraint) -> str:
1106        desc = expression.args.get("desc")
1107        if desc is not None:
1108            return f"PRIMARY KEY{' DESC' if desc else ' ASC'}"
1109        options = self.expressions(expression, key="options", flat=True, sep=" ")
1110        options = f" {options}" if options else ""
1111        return f"PRIMARY KEY{options}"
1112
1113    def uniquecolumnconstraint_sql(self, expression: exp.UniqueColumnConstraint) -> str:
1114        this = self.sql(expression, "this")
1115        this = f" {this}" if this else ""
1116        index_type = expression.args.get("index_type")
1117        index_type = f" USING {index_type}" if index_type else ""
1118        on_conflict = self.sql(expression, "on_conflict")
1119        on_conflict = f" {on_conflict}" if on_conflict else ""
1120        nulls_sql = " NULLS NOT DISTINCT" if expression.args.get("nulls") else ""
1121        options = self.expressions(expression, key="options", flat=True, sep=" ")
1122        options = f" {options}" if options else ""
1123        return f"UNIQUE{nulls_sql}{this}{index_type}{on_conflict}{options}"
1124
1125    def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
1126        return self.sql(expression, "this")
1127
1128    def create_sql(self, expression: exp.Create) -> str:
1129        kind = self.sql(expression, "kind")
1130        kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1131        properties = expression.args.get("properties")
1132        properties_locs = self.locate_properties(properties) if properties else defaultdict()
1133
1134        this = self.createable_sql(expression, properties_locs)
1135
1136        properties_sql = ""
1137        if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get(
1138            exp.Properties.Location.POST_WITH
1139        ):
1140            props_ast = exp.Properties(
1141                expressions=[
1142                    *properties_locs[exp.Properties.Location.POST_SCHEMA],
1143                    *properties_locs[exp.Properties.Location.POST_WITH],
1144                ]
1145            )
1146            props_ast.parent = expression
1147            properties_sql = self.sql(props_ast)
1148
1149            if properties_locs.get(exp.Properties.Location.POST_SCHEMA):
1150                properties_sql = self.sep() + properties_sql
1151            elif not self.pretty:
1152                # Standalone POST_WITH properties need a leading whitespace in non-pretty mode
1153                properties_sql = f" {properties_sql}"
1154
1155        begin = " BEGIN" if expression.args.get("begin") else ""
1156        end = " END" if expression.args.get("end") else ""
1157
1158        expression_sql = self.sql(expression, "expression")
1159        if expression_sql:
1160            expression_sql = f"{begin}{self.sep()}{expression_sql}{end}"
1161
1162            if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return):
1163                postalias_props_sql = ""
1164                if properties_locs.get(exp.Properties.Location.POST_ALIAS):
1165                    postalias_props_sql = self.properties(
1166                        exp.Properties(
1167                            expressions=properties_locs[exp.Properties.Location.POST_ALIAS]
1168                        ),
1169                        wrapped=False,
1170                    )
1171                postalias_props_sql = f" {postalias_props_sql}" if postalias_props_sql else ""
1172                expression_sql = f" AS{postalias_props_sql}{expression_sql}"
1173
1174        postindex_props_sql = ""
1175        if properties_locs.get(exp.Properties.Location.POST_INDEX):
1176            postindex_props_sql = self.properties(
1177                exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]),
1178                wrapped=False,
1179                prefix=" ",
1180            )
1181
1182        indexes = self.expressions(expression, key="indexes", indent=False, sep=" ")
1183        indexes = f" {indexes}" if indexes else ""
1184        index_sql = indexes + postindex_props_sql
1185
1186        replace = " OR REPLACE" if expression.args.get("replace") else ""
1187        refresh = " OR REFRESH" if expression.args.get("refresh") else ""
1188        unique = " UNIQUE" if expression.args.get("unique") else ""
1189
1190        clustered = expression.args.get("clustered")
1191        if clustered is None:
1192            clustered_sql = ""
1193        elif clustered:
1194            clustered_sql = " CLUSTERED COLUMNSTORE"
1195        else:
1196            clustered_sql = " NONCLUSTERED COLUMNSTORE"
1197
1198        postcreate_props_sql = ""
1199        if properties_locs.get(exp.Properties.Location.POST_CREATE):
1200            postcreate_props_sql = self.properties(
1201                exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]),
1202                sep=" ",
1203                prefix=" ",
1204                wrapped=False,
1205            )
1206
1207        modifiers = "".join((clustered_sql, replace, refresh, unique, postcreate_props_sql))
1208
1209        postexpression_props_sql = ""
1210        if properties_locs.get(exp.Properties.Location.POST_EXPRESSION):
1211            postexpression_props_sql = self.properties(
1212                exp.Properties(
1213                    expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION]
1214                ),
1215                sep=" ",
1216                prefix=" ",
1217                wrapped=False,
1218            )
1219
1220        concurrently = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1221        exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
1222        no_schema_binding = (
1223            " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else ""
1224        )
1225
1226        clone = self.sql(expression, "clone")
1227        clone = f" {clone}" if clone else ""
1228
1229        if kind in self.EXPRESSION_PRECEDES_PROPERTIES_CREATABLES:
1230            properties_expression = f"{expression_sql}{properties_sql}"
1231        else:
1232            properties_expression = f"{properties_sql}{expression_sql}"
1233
1234        expression_sql = f"CREATE{modifiers} {kind}{concurrently}{exists_sql} {this}{properties_expression}{postexpression_props_sql}{index_sql}{no_schema_binding}{clone}"
1235        return self.prepend_ctes(expression, expression_sql)
1236
1237    def sequenceproperties_sql(self, expression: exp.SequenceProperties) -> str:
1238        start = self.sql(expression, "start")
1239        start = f"START WITH {start}" if start else ""
1240        increment = self.sql(expression, "increment")
1241        increment = f" INCREMENT BY {increment}" if increment else ""
1242        minvalue = self.sql(expression, "minvalue")
1243        minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1244        maxvalue = self.sql(expression, "maxvalue")
1245        maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1246        owned = self.sql(expression, "owned")
1247        owned = f" OWNED BY {owned}" if owned else ""
1248
1249        cache = expression.args.get("cache")
1250        if cache is None:
1251            cache_str = ""
1252        elif cache is True:
1253            cache_str = " CACHE"
1254        else:
1255            cache_str = f" CACHE {cache}"
1256
1257        options = self.expressions(expression, key="options", flat=True, sep=" ")
1258        options = f" {options}" if options else ""
1259
1260        return f"{start}{increment}{minvalue}{maxvalue}{cache_str}{options}{owned}".lstrip()
1261
1262    def clone_sql(self, expression: exp.Clone) -> str:
1263        this = self.sql(expression, "this")
1264        shallow = "SHALLOW " if expression.args.get("shallow") else ""
1265        keyword = "COPY" if expression.args.get("copy") and self.SUPPORTS_TABLE_COPY else "CLONE"
1266        return f"{shallow}{keyword} {this}"
1267
1268    def describe_sql(self, expression: exp.Describe) -> str:
1269        style = expression.args.get("style")
1270        style = f" {style}" if style else ""
1271        partition = self.sql(expression, "partition")
1272        partition = f" {partition}" if partition else ""
1273        format = self.sql(expression, "format")
1274        format = f" {format}" if format else ""
1275
1276        return f"DESCRIBE{style}{format} {self.sql(expression, 'this')}{partition}"
1277
1278    def heredoc_sql(self, expression: exp.Heredoc) -> str:
1279        tag = self.sql(expression, "tag")
1280        return f"${tag}${self.sql(expression, 'this')}${tag}$"
1281
1282    def prepend_ctes(self, expression: exp.Expression, sql: str) -> str:
1283        with_ = self.sql(expression, "with")
1284        if with_:
1285            sql = f"{with_}{self.sep()}{sql}"
1286        return sql
1287
1288    def with_sql(self, expression: exp.With) -> str:
1289        sql = self.expressions(expression, flat=True)
1290        recursive = (
1291            "RECURSIVE "
1292            if self.CTE_RECURSIVE_KEYWORD_REQUIRED and expression.args.get("recursive")
1293            else ""
1294        )
1295        search = self.sql(expression, "search")
1296        search = f" {search}" if search else ""
1297
1298        return f"WITH {recursive}{sql}{search}"
1299
1300    def cte_sql(self, expression: exp.CTE) -> str:
1301        alias = expression.args.get("alias")
1302        if alias:
1303            alias.add_comments(expression.pop_comments())
1304
1305        alias_sql = self.sql(expression, "alias")
1306
1307        materialized = expression.args.get("materialized")
1308        if materialized is False:
1309            materialized = "NOT MATERIALIZED "
1310        elif materialized:
1311            materialized = "MATERIALIZED "
1312
1313        return f"{alias_sql} AS {materialized or ''}{self.wrap(expression)}"
1314
1315    def tablealias_sql(self, expression: exp.TableAlias) -> str:
1316        alias = self.sql(expression, "this")
1317        columns = self.expressions(expression, key="columns", flat=True)
1318        columns = f"({columns})" if columns else ""
1319
1320        if columns and not self.SUPPORTS_TABLE_ALIAS_COLUMNS:
1321            columns = ""
1322            self.unsupported("Named columns are not supported in table alias.")
1323
1324        if not alias and not self.dialect.UNNEST_COLUMN_ONLY:
1325            alias = self._next_name()
1326
1327        return f"{alias}{columns}"
1328
1329    def bitstring_sql(self, expression: exp.BitString) -> str:
1330        this = self.sql(expression, "this")
1331        if self.dialect.BIT_START:
1332            return f"{self.dialect.BIT_START}{this}{self.dialect.BIT_END}"
1333        return f"{int(this, 2)}"
1334
1335    def hexstring_sql(
1336        self, expression: exp.HexString, binary_function_repr: t.Optional[str] = None
1337    ) -> str:
1338        this = self.sql(expression, "this")
1339        is_integer_type = expression.args.get("is_integer")
1340
1341        if (is_integer_type and not self.dialect.HEX_STRING_IS_INTEGER_TYPE) or (
1342            not self.dialect.HEX_START and not binary_function_repr
1343        ):
1344            # Integer representation will be returned if:
1345            # - The read dialect treats the hex value as integer literal but not the write
1346            # - The transpilation is not supported (write dialect hasn't set HEX_START or the param flag)
1347            return f"{int(this, 16)}"
1348
1349        if not is_integer_type:
1350            # Read dialect treats the hex value as BINARY/BLOB
1351            if binary_function_repr:
1352                # The write dialect supports the transpilation to its equivalent BINARY/BLOB
1353                return self.func(binary_function_repr, exp.Literal.string(this))
1354            if self.dialect.HEX_STRING_IS_INTEGER_TYPE:
1355                # The write dialect does not support the transpilation, it'll treat the hex value as INTEGER
1356                self.unsupported("Unsupported transpilation from BINARY/BLOB hex string")
1357
1358        return f"{self.dialect.HEX_START}{this}{self.dialect.HEX_END}"
1359
1360    def bytestring_sql(self, expression: exp.ByteString) -> str:
1361        this = self.sql(expression, "this")
1362        if self.dialect.BYTE_START:
1363            return f"{self.dialect.BYTE_START}{this}{self.dialect.BYTE_END}"
1364        return this
1365
1366    def unicodestring_sql(self, expression: exp.UnicodeString) -> str:
1367        this = self.sql(expression, "this")
1368        escape = expression.args.get("escape")
1369
1370        if self.dialect.UNICODE_START:
1371            escape_substitute = r"\\\1"
1372            left_quote, right_quote = self.dialect.UNICODE_START, self.dialect.UNICODE_END
1373        else:
1374            escape_substitute = r"\\u\1"
1375            left_quote, right_quote = self.dialect.QUOTE_START, self.dialect.QUOTE_END
1376
1377        if escape:
1378            escape_pattern = re.compile(rf"{escape.name}(\d+)")
1379            escape_sql = f" UESCAPE {self.sql(escape)}" if self.SUPPORTS_UESCAPE else ""
1380        else:
1381            escape_pattern = ESCAPED_UNICODE_RE
1382            escape_sql = ""
1383
1384        if not self.dialect.UNICODE_START or (escape and not self.SUPPORTS_UESCAPE):
1385            this = escape_pattern.sub(escape_substitute, this)
1386
1387        return f"{left_quote}{this}{right_quote}{escape_sql}"
1388
1389    def rawstring_sql(self, expression: exp.RawString) -> str:
1390        string = expression.this
1391        if "\\" in self.dialect.tokenizer_class.STRING_ESCAPES:
1392            string = string.replace("\\", "\\\\")
1393
1394        string = self.escape_str(string, escape_backslash=False)
1395        return f"{self.dialect.QUOTE_START}{string}{self.dialect.QUOTE_END}"
1396
1397    def datatypeparam_sql(self, expression: exp.DataTypeParam) -> str:
1398        this = self.sql(expression, "this")
1399        specifier = self.sql(expression, "expression")
1400        specifier = f" {specifier}" if specifier and self.DATA_TYPE_SPECIFIERS_ALLOWED else ""
1401        return f"{this}{specifier}"
1402
1403    def datatype_sql(self, expression: exp.DataType) -> str:
1404        nested = ""
1405        values = ""
1406        interior = self.expressions(expression, flat=True)
1407
1408        type_value = expression.this
1409        if type_value == exp.DataType.Type.USERDEFINED and expression.args.get("kind"):
1410            type_sql = self.sql(expression, "kind")
1411        else:
1412            type_sql = (
1413                self.TYPE_MAPPING.get(type_value, type_value.value)
1414                if isinstance(type_value, exp.DataType.Type)
1415                else type_value
1416            )
1417
1418        if interior:
1419            if expression.args.get("nested"):
1420                nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}"
1421                if expression.args.get("values") is not None:
1422                    delimiters = ("[", "]") if type_value == exp.DataType.Type.ARRAY else ("(", ")")
1423                    values = self.expressions(expression, key="values", flat=True)
1424                    values = f"{delimiters[0]}{values}{delimiters[1]}"
1425            elif type_value == exp.DataType.Type.INTERVAL:
1426                nested = f" {interior}"
1427            else:
1428                nested = f"({interior})"
1429
1430        type_sql = f"{type_sql}{nested}{values}"
1431        if self.TZ_TO_WITH_TIME_ZONE and type_value in (
1432            exp.DataType.Type.TIMETZ,
1433            exp.DataType.Type.TIMESTAMPTZ,
1434        ):
1435            type_sql = f"{type_sql} WITH TIME ZONE"
1436
1437        return type_sql
1438
1439    def directory_sql(self, expression: exp.Directory) -> str:
1440        local = "LOCAL " if expression.args.get("local") else ""
1441        row_format = self.sql(expression, "row_format")
1442        row_format = f" {row_format}" if row_format else ""
1443        return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}"
1444
1445    def delete_sql(self, expression: exp.Delete) -> str:
1446        this = self.sql(expression, "this")
1447        this = f" FROM {this}" if this else ""
1448        using = self.sql(expression, "using")
1449        using = f" USING {using}" if using else ""
1450        cluster = self.sql(expression, "cluster")
1451        cluster = f" {cluster}" if cluster else ""
1452        where = self.sql(expression, "where")
1453        returning = self.sql(expression, "returning")
1454        limit = self.sql(expression, "limit")
1455        tables = self.expressions(expression, key="tables")
1456        tables = f" {tables}" if tables else ""
1457        if self.RETURNING_END:
1458            expression_sql = f"{this}{using}{cluster}{where}{returning}{limit}"
1459        else:
1460            expression_sql = f"{returning}{this}{using}{cluster}{where}{limit}"
1461        return self.prepend_ctes(expression, f"DELETE{tables}{expression_sql}")
1462
1463    def drop_sql(self, expression: exp.Drop) -> str:
1464        this = self.sql(expression, "this")
1465        expressions = self.expressions(expression, flat=True)
1466        expressions = f" ({expressions})" if expressions else ""
1467        kind = expression.args["kind"]
1468        kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1469        exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
1470        concurrently_sql = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1471        on_cluster = self.sql(expression, "cluster")
1472        on_cluster = f" {on_cluster}" if on_cluster else ""
1473        temporary = " TEMPORARY" if expression.args.get("temporary") else ""
1474        materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
1475        cascade = " CASCADE" if expression.args.get("cascade") else ""
1476        constraints = " CONSTRAINTS" if expression.args.get("constraints") else ""
1477        purge = " PURGE" if expression.args.get("purge") else ""
1478        return f"DROP{temporary}{materialized} {kind}{concurrently_sql}{exists_sql}{this}{on_cluster}{expressions}{cascade}{constraints}{purge}"
1479
1480    def set_operation(self, expression: exp.SetOperation) -> str:
1481        op_type = type(expression)
1482        op_name = op_type.key.upper()
1483
1484        distinct = expression.args.get("distinct")
1485        if (
1486            distinct is False
1487            and op_type in (exp.Except, exp.Intersect)
1488            and not self.EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE
1489        ):
1490            self.unsupported(f"{op_name} ALL is not supported")
1491
1492        default_distinct = self.dialect.SET_OP_DISTINCT_BY_DEFAULT[op_type]
1493
1494        if distinct is None:
1495            distinct = default_distinct
1496            if distinct is None:
1497                self.unsupported(f"{op_name} requires DISTINCT or ALL to be specified")
1498
1499        if distinct is default_distinct:
1500            distinct_or_all = ""
1501        else:
1502            distinct_or_all = " DISTINCT" if distinct else " ALL"
1503
1504        side_kind = " ".join(filter(None, [expression.side, expression.kind]))
1505        side_kind = f"{side_kind} " if side_kind else ""
1506
1507        by_name = " BY NAME" if expression.args.get("by_name") else ""
1508        on = self.expressions(expression, key="on", flat=True)
1509        on = f" ON ({on})" if on else ""
1510
1511        return f"{side_kind}{op_name}{distinct_or_all}{by_name}{on}"
1512
1513    def set_operations(self, expression: exp.SetOperation) -> str:
1514        if not self.SET_OP_MODIFIERS:
1515            limit = expression.args.get("limit")
1516            order = expression.args.get("order")
1517
1518            if limit or order:
1519                select = self._move_ctes_to_top_level(
1520                    exp.subquery(expression, "_l_0", copy=False).select("*", copy=False)
1521                )
1522
1523                if limit:
1524                    select = select.limit(limit.pop(), copy=False)
1525                if order:
1526                    select = select.order_by(order.pop(), copy=False)
1527                return self.sql(select)
1528
1529        sqls: t.List[str] = []
1530        stack: t.List[t.Union[str, exp.Expression]] = [expression]
1531
1532        while stack:
1533            node = stack.pop()
1534
1535            if isinstance(node, exp.SetOperation):
1536                stack.append(node.expression)
1537                stack.append(
1538                    self.maybe_comment(
1539                        self.set_operation(node), comments=node.comments, separated=True
1540                    )
1541                )
1542                stack.append(node.this)
1543            else:
1544                sqls.append(self.sql(node))
1545
1546        this = self.sep().join(sqls)
1547        this = self.query_modifiers(expression, this)
1548        return self.prepend_ctes(expression, this)
1549
1550    def fetch_sql(self, expression: exp.Fetch) -> str:
1551        direction = expression.args.get("direction")
1552        direction = f" {direction}" if direction else ""
1553        count = self.sql(expression, "count")
1554        count = f" {count}" if count else ""
1555        limit_options = self.sql(expression, "limit_options")
1556        limit_options = f"{limit_options}" if limit_options else " ROWS ONLY"
1557        return f"{self.seg('FETCH')}{direction}{count}{limit_options}"
1558
1559    def limitoptions_sql(self, expression: exp.LimitOptions) -> str:
1560        percent = " PERCENT" if expression.args.get("percent") else ""
1561        rows = " ROWS" if expression.args.get("rows") else ""
1562        with_ties = " WITH TIES" if expression.args.get("with_ties") else ""
1563        if not with_ties and rows:
1564            with_ties = " ONLY"
1565        return f"{percent}{rows}{with_ties}"
1566
1567    def filter_sql(self, expression: exp.Filter) -> str:
1568        if self.AGGREGATE_FILTER_SUPPORTED:
1569            this = self.sql(expression, "this")
1570            where = self.sql(expression, "expression").strip()
1571            return f"{this} FILTER({where})"
1572
1573        agg = expression.this
1574        agg_arg = agg.this
1575        cond = expression.expression.this
1576        agg_arg.replace(exp.If(this=cond.copy(), true=agg_arg.copy()))
1577        return self.sql(agg)
1578
1579    def hint_sql(self, expression: exp.Hint) -> str:
1580        if not self.QUERY_HINTS:
1581            self.unsupported("Hints are not supported")
1582            return ""
1583
1584        return f" /*+ {self.expressions(expression, sep=self.QUERY_HINT_SEP).strip()} */"
1585
1586    def indexparameters_sql(self, expression: exp.IndexParameters) -> str:
1587        using = self.sql(expression, "using")
1588        using = f" USING {using}" if using else ""
1589        columns = self.expressions(expression, key="columns", flat=True)
1590        columns = f"({columns})" if columns else ""
1591        partition_by = self.expressions(expression, key="partition_by", flat=True)
1592        partition_by = f" PARTITION BY {partition_by}" if partition_by else ""
1593        where = self.sql(expression, "where")
1594        include = self.expressions(expression, key="include", flat=True)
1595        if include:
1596            include = f" INCLUDE ({include})"
1597        with_storage = self.expressions(expression, key="with_storage", flat=True)
1598        with_storage = f" WITH ({with_storage})" if with_storage else ""
1599        tablespace = self.sql(expression, "tablespace")
1600        tablespace = f" USING INDEX TABLESPACE {tablespace}" if tablespace else ""
1601        on = self.sql(expression, "on")
1602        on = f" ON {on}" if on else ""
1603
1604        return f"{using}{columns}{include}{with_storage}{tablespace}{partition_by}{where}{on}"
1605
1606    def index_sql(self, expression: exp.Index) -> str:
1607        unique = "UNIQUE " if expression.args.get("unique") else ""
1608        primary = "PRIMARY " if expression.args.get("primary") else ""
1609        amp = "AMP " if expression.args.get("amp") else ""
1610        name = self.sql(expression, "this")
1611        name = f"{name} " if name else ""
1612        table = self.sql(expression, "table")
1613        table = f"{self.INDEX_ON} {table}" if table else ""
1614
1615        index = "INDEX " if not table else ""
1616
1617        params = self.sql(expression, "params")
1618        return f"{unique}{primary}{amp}{index}{name}{table}{params}"
1619
1620    def identifier_sql(self, expression: exp.Identifier) -> str:
1621        text = expression.name
1622        lower = text.lower()
1623        text = lower if self.normalize and not expression.quoted else text
1624        text = text.replace(self._identifier_end, self._escaped_identifier_end)
1625        if (
1626            expression.quoted
1627            or self.dialect.can_identify(text, self.identify)
1628            or lower in self.RESERVED_KEYWORDS
1629            or (not self.dialect.IDENTIFIERS_CAN_START_WITH_DIGIT and text[:1].isdigit())
1630        ):
1631            text = f"{self._identifier_start}{text}{self._identifier_end}"
1632        return text
1633
1634    def hex_sql(self, expression: exp.Hex) -> str:
1635        text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1636        if self.dialect.HEX_LOWERCASE:
1637            text = self.func("LOWER", text)
1638
1639        return text
1640
1641    def lowerhex_sql(self, expression: exp.LowerHex) -> str:
1642        text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1643        if not self.dialect.HEX_LOWERCASE:
1644            text = self.func("LOWER", text)
1645        return text
1646
1647    def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str:
1648        input_format = self.sql(expression, "input_format")
1649        input_format = f"INPUTFORMAT {input_format}" if input_format else ""
1650        output_format = self.sql(expression, "output_format")
1651        output_format = f"OUTPUTFORMAT {output_format}" if output_format else ""
1652        return self.sep().join((input_format, output_format))
1653
1654    def national_sql(self, expression: exp.National, prefix: str = "N") -> str:
1655        string = self.sql(exp.Literal.string(expression.name))
1656        return f"{prefix}{string}"
1657
1658    def partition_sql(self, expression: exp.Partition) -> str:
1659        partition_keyword = "SUBPARTITION" if expression.args.get("subpartition") else "PARTITION"
1660        return f"{partition_keyword}({self.expressions(expression, flat=True)})"
1661
1662    def properties_sql(self, expression: exp.Properties) -> str:
1663        root_properties = []
1664        with_properties = []
1665
1666        for p in expression.expressions:
1667            p_loc = self.PROPERTIES_LOCATION[p.__class__]
1668            if p_loc == exp.Properties.Location.POST_WITH:
1669                with_properties.append(p)
1670            elif p_loc == exp.Properties.Location.POST_SCHEMA:
1671                root_properties.append(p)
1672
1673        root_props_ast = exp.Properties(expressions=root_properties)
1674        root_props_ast.parent = expression.parent
1675
1676        with_props_ast = exp.Properties(expressions=with_properties)
1677        with_props_ast.parent = expression.parent
1678
1679        root_props = self.root_properties(root_props_ast)
1680        with_props = self.with_properties(with_props_ast)
1681
1682        if root_props and with_props and not self.pretty:
1683            with_props = " " + with_props
1684
1685        return root_props + with_props
1686
1687    def root_properties(self, properties: exp.Properties) -> str:
1688        if properties.expressions:
1689            return self.expressions(properties, indent=False, sep=" ")
1690        return ""
1691
1692    def properties(
1693        self,
1694        properties: exp.Properties,
1695        prefix: str = "",
1696        sep: str = ", ",
1697        suffix: str = "",
1698        wrapped: bool = True,
1699    ) -> str:
1700        if properties.expressions:
1701            expressions = self.expressions(properties, sep=sep, indent=False)
1702            if expressions:
1703                expressions = self.wrap(expressions) if wrapped else expressions
1704                return f"{prefix}{' ' if prefix.strip() else ''}{expressions}{suffix}"
1705        return ""
1706
1707    def with_properties(self, properties: exp.Properties) -> str:
1708        return self.properties(properties, prefix=self.seg(self.WITH_PROPERTIES_PREFIX, sep=""))
1709
1710    def locate_properties(self, properties: exp.Properties) -> t.DefaultDict:
1711        properties_locs = defaultdict(list)
1712        for p in properties.expressions:
1713            p_loc = self.PROPERTIES_LOCATION[p.__class__]
1714            if p_loc != exp.Properties.Location.UNSUPPORTED:
1715                properties_locs[p_loc].append(p)
1716            else:
1717                self.unsupported(f"Unsupported property {p.key}")
1718
1719        return properties_locs
1720
1721    def property_name(self, expression: exp.Property, string_key: bool = False) -> str:
1722        if isinstance(expression.this, exp.Dot):
1723            return self.sql(expression, "this")
1724        return f"'{expression.name}'" if string_key else expression.name
1725
1726    def property_sql(self, expression: exp.Property) -> str:
1727        property_cls = expression.__class__
1728        if property_cls == exp.Property:
1729            return f"{self.property_name(expression)}={self.sql(expression, 'value')}"
1730
1731        property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls)
1732        if not property_name:
1733            self.unsupported(f"Unsupported property {expression.key}")
1734
1735        return f"{property_name}={self.sql(expression, 'this')}"
1736
1737    def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
1738        if self.SUPPORTS_CREATE_TABLE_LIKE:
1739            options = " ".join(f"{e.name} {self.sql(e, 'value')}" for e in expression.expressions)
1740            options = f" {options}" if options else ""
1741
1742            like = f"LIKE {self.sql(expression, 'this')}{options}"
1743            if self.LIKE_PROPERTY_INSIDE_SCHEMA and not isinstance(expression.parent, exp.Schema):
1744                like = f"({like})"
1745
1746            return like
1747
1748        if expression.expressions:
1749            self.unsupported("Transpilation of LIKE property options is unsupported")
1750
1751        select = exp.select("*").from_(expression.this).limit(0)
1752        return f"AS {self.sql(select)}"
1753
1754    def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str:
1755        no = "NO " if expression.args.get("no") else ""
1756        protection = " PROTECTION" if expression.args.get("protection") else ""
1757        return f"{no}FALLBACK{protection}"
1758
1759    def journalproperty_sql(self, expression: exp.JournalProperty) -> str:
1760        no = "NO " if expression.args.get("no") else ""
1761        local = expression.args.get("local")
1762        local = f"{local} " if local else ""
1763        dual = "DUAL " if expression.args.get("dual") else ""
1764        before = "BEFORE " if expression.args.get("before") else ""
1765        after = "AFTER " if expression.args.get("after") else ""
1766        return f"{no}{local}{dual}{before}{after}JOURNAL"
1767
1768    def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str:
1769        freespace = self.sql(expression, "this")
1770        percent = " PERCENT" if expression.args.get("percent") else ""
1771        return f"FREESPACE={freespace}{percent}"
1772
1773    def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str:
1774        if expression.args.get("default"):
1775            property = "DEFAULT"
1776        elif expression.args.get("on"):
1777            property = "ON"
1778        else:
1779            property = "OFF"
1780        return f"CHECKSUM={property}"
1781
1782    def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str:
1783        if expression.args.get("no"):
1784            return "NO MERGEBLOCKRATIO"
1785        if expression.args.get("default"):
1786            return "DEFAULT MERGEBLOCKRATIO"
1787
1788        percent = " PERCENT" if expression.args.get("percent") else ""
1789        return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}"
1790
1791    def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str:
1792        default = expression.args.get("default")
1793        minimum = expression.args.get("minimum")
1794        maximum = expression.args.get("maximum")
1795        if default or minimum or maximum:
1796            if default:
1797                prop = "DEFAULT"
1798            elif minimum:
1799                prop = "MINIMUM"
1800            else:
1801                prop = "MAXIMUM"
1802            return f"{prop} DATABLOCKSIZE"
1803        units = expression.args.get("units")
1804        units = f" {units}" if units else ""
1805        return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}"
1806
1807    def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str:
1808        autotemp = expression.args.get("autotemp")
1809        always = expression.args.get("always")
1810        default = expression.args.get("default")
1811        manual = expression.args.get("manual")
1812        never = expression.args.get("never")
1813
1814        if autotemp is not None:
1815            prop = f"AUTOTEMP({self.expressions(autotemp)})"
1816        elif always:
1817            prop = "ALWAYS"
1818        elif default:
1819            prop = "DEFAULT"
1820        elif manual:
1821            prop = "MANUAL"
1822        elif never:
1823            prop = "NEVER"
1824        return f"BLOCKCOMPRESSION={prop}"
1825
1826    def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str:
1827        no = expression.args.get("no")
1828        no = " NO" if no else ""
1829        concurrent = expression.args.get("concurrent")
1830        concurrent = " CONCURRENT" if concurrent else ""
1831        target = self.sql(expression, "target")
1832        target = f" {target}" if target else ""
1833        return f"WITH{no}{concurrent} ISOLATED LOADING{target}"
1834
1835    def partitionboundspec_sql(self, expression: exp.PartitionBoundSpec) -> str:
1836        if isinstance(expression.this, list):
1837            return f"IN ({self.expressions(expression, key='this', flat=True)})"
1838        if expression.this:
1839            modulus = self.sql(expression, "this")
1840            remainder = self.sql(expression, "expression")
1841            return f"WITH (MODULUS {modulus}, REMAINDER {remainder})"
1842
1843        from_expressions = self.expressions(expression, key="from_expressions", flat=True)
1844        to_expressions = self.expressions(expression, key="to_expressions", flat=True)
1845        return f"FROM ({from_expressions}) TO ({to_expressions})"
1846
1847    def partitionedofproperty_sql(self, expression: exp.PartitionedOfProperty) -> str:
1848        this = self.sql(expression, "this")
1849
1850        for_values_or_default = expression.expression
1851        if isinstance(for_values_or_default, exp.PartitionBoundSpec):
1852            for_values_or_default = f" FOR VALUES {self.sql(for_values_or_default)}"
1853        else:
1854            for_values_or_default = " DEFAULT"
1855
1856        return f"PARTITION OF {this}{for_values_or_default}"
1857
1858    def lockingproperty_sql(self, expression: exp.LockingProperty) -> str:
1859        kind = expression.args.get("kind")
1860        this = f" {self.sql(expression, 'this')}" if expression.this else ""
1861        for_or_in = expression.args.get("for_or_in")
1862        for_or_in = f" {for_or_in}" if for_or_in else ""
1863        lock_type = expression.args.get("lock_type")
1864        override = " OVERRIDE" if expression.args.get("override") else ""
1865        return f"LOCKING {kind}{this}{for_or_in} {lock_type}{override}"
1866
1867    def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str:
1868        data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA"
1869        statistics = expression.args.get("statistics")
1870        statistics_sql = ""
1871        if statistics is not None:
1872            statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS"
1873        return f"{data_sql}{statistics_sql}"
1874
1875    def withsystemversioningproperty_sql(self, expression: exp.WithSystemVersioningProperty) -> str:
1876        this = self.sql(expression, "this")
1877        this = f"HISTORY_TABLE={this}" if this else ""
1878        data_consistency: t.Optional[str] = self.sql(expression, "data_consistency")
1879        data_consistency = (
1880            f"DATA_CONSISTENCY_CHECK={data_consistency}" if data_consistency else None
1881        )
1882        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
1883        retention_period = (
1884            f"HISTORY_RETENTION_PERIOD={retention_period}" if retention_period else None
1885        )
1886
1887        if this:
1888            on_sql = self.func("ON", this, data_consistency, retention_period)
1889        else:
1890            on_sql = "ON" if expression.args.get("on") else "OFF"
1891
1892        sql = f"SYSTEM_VERSIONING={on_sql}"
1893
1894        return f"WITH({sql})" if expression.args.get("with") else sql
1895
1896    def insert_sql(self, expression: exp.Insert) -> str:
1897        hint = self.sql(expression, "hint")
1898        overwrite = expression.args.get("overwrite")
1899
1900        if isinstance(expression.this, exp.Directory):
1901            this = " OVERWRITE" if overwrite else " INTO"
1902        else:
1903            this = self.INSERT_OVERWRITE if overwrite else " INTO"
1904
1905        stored = self.sql(expression, "stored")
1906        stored = f" {stored}" if stored else ""
1907        alternative = expression.args.get("alternative")
1908        alternative = f" OR {alternative}" if alternative else ""
1909        ignore = " IGNORE" if expression.args.get("ignore") else ""
1910        is_function = expression.args.get("is_function")
1911        if is_function:
1912            this = f"{this} FUNCTION"
1913        this = f"{this} {self.sql(expression, 'this')}"
1914
1915        exists = " IF EXISTS" if expression.args.get("exists") else ""
1916        where = self.sql(expression, "where")
1917        where = f"{self.sep()}REPLACE WHERE {where}" if where else ""
1918        expression_sql = f"{self.sep()}{self.sql(expression, 'expression')}"
1919        on_conflict = self.sql(expression, "conflict")
1920        on_conflict = f" {on_conflict}" if on_conflict else ""
1921        by_name = " BY NAME" if expression.args.get("by_name") else ""
1922        returning = self.sql(expression, "returning")
1923
1924        if self.RETURNING_END:
1925            expression_sql = f"{expression_sql}{on_conflict}{returning}"
1926        else:
1927            expression_sql = f"{returning}{expression_sql}{on_conflict}"
1928
1929        partition_by = self.sql(expression, "partition")
1930        partition_by = f" {partition_by}" if partition_by else ""
1931        settings = self.sql(expression, "settings")
1932        settings = f" {settings}" if settings else ""
1933
1934        source = self.sql(expression, "source")
1935        source = f"TABLE {source}" if source else ""
1936
1937        sql = f"INSERT{hint}{alternative}{ignore}{this}{stored}{by_name}{exists}{partition_by}{settings}{where}{expression_sql}{source}"
1938        return self.prepend_ctes(expression, sql)
1939
1940    def introducer_sql(self, expression: exp.Introducer) -> str:
1941        return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
1942
1943    def kill_sql(self, expression: exp.Kill) -> str:
1944        kind = self.sql(expression, "kind")
1945        kind = f" {kind}" if kind else ""
1946        this = self.sql(expression, "this")
1947        this = f" {this}" if this else ""
1948        return f"KILL{kind}{this}"
1949
1950    def pseudotype_sql(self, expression: exp.PseudoType) -> str:
1951        return expression.name
1952
1953    def objectidentifier_sql(self, expression: exp.ObjectIdentifier) -> str:
1954        return expression.name
1955
1956    def onconflict_sql(self, expression: exp.OnConflict) -> str:
1957        conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT"
1958
1959        constraint = self.sql(expression, "constraint")
1960        constraint = f" ON CONSTRAINT {constraint}" if constraint else ""
1961
1962        conflict_keys = self.expressions(expression, key="conflict_keys", flat=True)
1963        conflict_keys = f"({conflict_keys}) " if conflict_keys else " "
1964        action = self.sql(expression, "action")
1965
1966        expressions = self.expressions(expression, flat=True)
1967        if expressions:
1968            set_keyword = "SET " if self.DUPLICATE_KEY_UPDATE_WITH_SET else ""
1969            expressions = f" {set_keyword}{expressions}"
1970
1971        where = self.sql(expression, "where")
1972        return f"{conflict}{constraint}{conflict_keys}{action}{expressions}{where}"
1973
1974    def returning_sql(self, expression: exp.Returning) -> str:
1975        return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}"
1976
1977    def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str:
1978        fields = self.sql(expression, "fields")
1979        fields = f" FIELDS TERMINATED BY {fields}" if fields else ""
1980        escaped = self.sql(expression, "escaped")
1981        escaped = f" ESCAPED BY {escaped}" if escaped else ""
1982        items = self.sql(expression, "collection_items")
1983        items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else ""
1984        keys = self.sql(expression, "map_keys")
1985        keys = f" MAP KEYS TERMINATED BY {keys}" if keys else ""
1986        lines = self.sql(expression, "lines")
1987        lines = f" LINES TERMINATED BY {lines}" if lines else ""
1988        null = self.sql(expression, "null")
1989        null = f" NULL DEFINED AS {null}" if null else ""
1990        return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}"
1991
1992    def withtablehint_sql(self, expression: exp.WithTableHint) -> str:
1993        return f"WITH ({self.expressions(expression, flat=True)})"
1994
1995    def indextablehint_sql(self, expression: exp.IndexTableHint) -> str:
1996        this = f"{self.sql(expression, 'this')} INDEX"
1997        target = self.sql(expression, "target")
1998        target = f" FOR {target}" if target else ""
1999        return f"{this}{target} ({self.expressions(expression, flat=True)})"
2000
2001    def historicaldata_sql(self, expression: exp.HistoricalData) -> str:
2002        this = self.sql(expression, "this")
2003        kind = self.sql(expression, "kind")
2004        expr = self.sql(expression, "expression")
2005        return f"{this} ({kind} => {expr})"
2006
2007    def table_parts(self, expression: exp.Table) -> str:
2008        return ".".join(
2009            self.sql(part)
2010            for part in (
2011                expression.args.get("catalog"),
2012                expression.args.get("db"),
2013                expression.args.get("this"),
2014            )
2015            if part is not None
2016        )
2017
2018    def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str:
2019        table = self.table_parts(expression)
2020        only = "ONLY " if expression.args.get("only") else ""
2021        partition = self.sql(expression, "partition")
2022        partition = f" {partition}" if partition else ""
2023        version = self.sql(expression, "version")
2024        version = f" {version}" if version else ""
2025        alias = self.sql(expression, "alias")
2026        alias = f"{sep}{alias}" if alias else ""
2027
2028        sample = self.sql(expression, "sample")
2029        if self.dialect.ALIAS_POST_TABLESAMPLE:
2030            sample_pre_alias = sample
2031            sample_post_alias = ""
2032        else:
2033            sample_pre_alias = ""
2034            sample_post_alias = sample
2035
2036        hints = self.expressions(expression, key="hints", sep=" ")
2037        hints = f" {hints}" if hints and self.TABLE_HINTS else ""
2038        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2039        joins = self.indent(
2040            self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2041        )
2042        laterals = self.expressions(expression, key="laterals", sep="")
2043
2044        file_format = self.sql(expression, "format")
2045        if file_format:
2046            pattern = self.sql(expression, "pattern")
2047            pattern = f", PATTERN => {pattern}" if pattern else ""
2048            file_format = f" (FILE_FORMAT => {file_format}{pattern})"
2049
2050        ordinality = expression.args.get("ordinality") or ""
2051        if ordinality:
2052            ordinality = f" WITH ORDINALITY{alias}"
2053            alias = ""
2054
2055        when = self.sql(expression, "when")
2056        if when:
2057            table = f"{table} {when}"
2058
2059        changes = self.sql(expression, "changes")
2060        changes = f" {changes}" if changes else ""
2061
2062        rows_from = self.expressions(expression, key="rows_from")
2063        if rows_from:
2064            table = f"ROWS FROM {self.wrap(rows_from)}"
2065
2066        return f"{only}{table}{changes}{partition}{version}{file_format}{sample_pre_alias}{alias}{hints}{pivots}{sample_post_alias}{joins}{laterals}{ordinality}"
2067
2068    def tablefromrows_sql(self, expression: exp.TableFromRows) -> str:
2069        table = self.func("TABLE", expression.this)
2070        alias = self.sql(expression, "alias")
2071        alias = f" AS {alias}" if alias else ""
2072        sample = self.sql(expression, "sample")
2073        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2074        joins = self.indent(
2075            self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2076        )
2077        return f"{table}{alias}{pivots}{sample}{joins}"
2078
2079    def tablesample_sql(
2080        self,
2081        expression: exp.TableSample,
2082        tablesample_keyword: t.Optional[str] = None,
2083    ) -> str:
2084        method = self.sql(expression, "method")
2085        method = f"{method} " if method and self.TABLESAMPLE_WITH_METHOD else ""
2086        numerator = self.sql(expression, "bucket_numerator")
2087        denominator = self.sql(expression, "bucket_denominator")
2088        field = self.sql(expression, "bucket_field")
2089        field = f" ON {field}" if field else ""
2090        bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else ""
2091        seed = self.sql(expression, "seed")
2092        seed = f" {self.TABLESAMPLE_SEED_KEYWORD} ({seed})" if seed else ""
2093
2094        size = self.sql(expression, "size")
2095        if size and self.TABLESAMPLE_SIZE_IS_ROWS:
2096            size = f"{size} ROWS"
2097
2098        percent = self.sql(expression, "percent")
2099        if percent and not self.dialect.TABLESAMPLE_SIZE_IS_PERCENT:
2100            percent = f"{percent} PERCENT"
2101
2102        expr = f"{bucket}{percent}{size}"
2103        if self.TABLESAMPLE_REQUIRES_PARENS:
2104            expr = f"({expr})"
2105
2106        return f" {tablesample_keyword or self.TABLESAMPLE_KEYWORDS} {method}{expr}{seed}"
2107
2108    def pivot_sql(self, expression: exp.Pivot) -> str:
2109        expressions = self.expressions(expression, flat=True)
2110        direction = "UNPIVOT" if expression.unpivot else "PIVOT"
2111
2112        group = self.sql(expression, "group")
2113
2114        if expression.this:
2115            this = self.sql(expression, "this")
2116            if not expressions:
2117                return f"UNPIVOT {this}"
2118
2119            on = f"{self.seg('ON')} {expressions}"
2120            into = self.sql(expression, "into")
2121            into = f"{self.seg('INTO')} {into}" if into else ""
2122            using = self.expressions(expression, key="using", flat=True)
2123            using = f"{self.seg('USING')} {using}" if using else ""
2124            return f"{direction} {this}{on}{into}{using}{group}"
2125
2126        alias = self.sql(expression, "alias")
2127        alias = f" AS {alias}" if alias else ""
2128
2129        fields = self.expressions(
2130            expression,
2131            "fields",
2132            sep=" ",
2133            dynamic=True,
2134            new_line=True,
2135            skip_first=True,
2136            skip_last=True,
2137        )
2138
2139        include_nulls = expression.args.get("include_nulls")
2140        if include_nulls is not None:
2141            nulls = " INCLUDE NULLS " if include_nulls else " EXCLUDE NULLS "
2142        else:
2143            nulls = ""
2144
2145        default_on_null = self.sql(expression, "default_on_null")
2146        default_on_null = f" DEFAULT ON NULL ({default_on_null})" if default_on_null else ""
2147        return f"{self.seg(direction)}{nulls}({expressions} FOR {fields}{default_on_null}{group}){alias}"
2148
2149    def version_sql(self, expression: exp.Version) -> str:
2150        this = f"FOR {expression.name}"
2151        kind = expression.text("kind")
2152        expr = self.sql(expression, "expression")
2153        return f"{this} {kind} {expr}"
2154
2155    def tuple_sql(self, expression: exp.Tuple) -> str:
2156        return f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
2157
2158    def update_sql(self, expression: exp.Update) -> str:
2159        this = self.sql(expression, "this")
2160        set_sql = self.expressions(expression, flat=True)
2161        from_sql = self.sql(expression, "from")
2162        where_sql = self.sql(expression, "where")
2163        returning = self.sql(expression, "returning")
2164        order = self.sql(expression, "order")
2165        limit = self.sql(expression, "limit")
2166        if self.RETURNING_END:
2167            expression_sql = f"{from_sql}{where_sql}{returning}"
2168        else:
2169            expression_sql = f"{returning}{from_sql}{where_sql}"
2170        sql = f"UPDATE {this} SET {set_sql}{expression_sql}{order}{limit}"
2171        return self.prepend_ctes(expression, sql)
2172
2173    def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
2174        values_as_table = values_as_table and self.VALUES_AS_TABLE
2175
2176        # The VALUES clause is still valid in an `INSERT INTO ..` statement, for example
2177        if values_as_table or not expression.find_ancestor(exp.From, exp.Join):
2178            args = self.expressions(expression)
2179            alias = self.sql(expression, "alias")
2180            values = f"VALUES{self.seg('')}{args}"
2181            values = (
2182                f"({values})"
2183                if self.WRAP_DERIVED_VALUES
2184                and (alias or isinstance(expression.parent, (exp.From, exp.Table)))
2185                else values
2186            )
2187            return f"{values} AS {alias}" if alias else values
2188
2189        # Converts `VALUES...` expression into a series of select unions.
2190        alias_node = expression.args.get("alias")
2191        column_names = alias_node and alias_node.columns
2192
2193        selects: t.List[exp.Query] = []
2194
2195        for i, tup in enumerate(expression.expressions):
2196            row = tup.expressions
2197
2198            if i == 0 and column_names:
2199                row = [
2200                    exp.alias_(value, column_name) for value, column_name in zip(row, column_names)
2201                ]
2202
2203            selects.append(exp.Select(expressions=row))
2204
2205        if self.pretty:
2206            # This may result in poor performance for large-cardinality `VALUES` tables, due to
2207            # the deep nesting of the resulting exp.Unions. If this is a problem, either increase
2208            # `sys.setrecursionlimit` to avoid RecursionErrors, or don't set `pretty`.
2209            query = reduce(lambda x, y: exp.union(x, y, distinct=False, copy=False), selects)
2210            return self.subquery_sql(query.subquery(alias_node and alias_node.this, copy=False))
2211
2212        alias = f" AS {self.sql(alias_node, 'this')}" if alias_node else ""
2213        unions = " UNION ALL ".join(self.sql(select) for select in selects)
2214        return f"({unions}){alias}"
2215
2216    def var_sql(self, expression: exp.Var) -> str:
2217        return self.sql(expression, "this")
2218
2219    @unsupported_args("expressions")
2220    def into_sql(self, expression: exp.Into) -> str:
2221        temporary = " TEMPORARY" if expression.args.get("temporary") else ""
2222        unlogged = " UNLOGGED" if expression.args.get("unlogged") else ""
2223        return f"{self.seg('INTO')}{temporary or unlogged} {self.sql(expression, 'this')}"
2224
2225    def from_sql(self, expression: exp.From) -> str:
2226        return f"{self.seg('FROM')} {self.sql(expression, 'this')}"
2227
2228    def groupingsets_sql(self, expression: exp.GroupingSets) -> str:
2229        grouping_sets = self.expressions(expression, indent=False)
2230        return f"GROUPING SETS {self.wrap(grouping_sets)}"
2231
2232    def rollup_sql(self, expression: exp.Rollup) -> str:
2233        expressions = self.expressions(expression, indent=False)
2234        return f"ROLLUP {self.wrap(expressions)}" if expressions else "WITH ROLLUP"
2235
2236    def cube_sql(self, expression: exp.Cube) -> str:
2237        expressions = self.expressions(expression, indent=False)
2238        return f"CUBE {self.wrap(expressions)}" if expressions else "WITH CUBE"
2239
2240    def group_sql(self, expression: exp.Group) -> str:
2241        group_by_all = expression.args.get("all")
2242        if group_by_all is True:
2243            modifier = " ALL"
2244        elif group_by_all is False:
2245            modifier = " DISTINCT"
2246        else:
2247            modifier = ""
2248
2249        group_by = self.op_expressions(f"GROUP BY{modifier}", expression)
2250
2251        grouping_sets = self.expressions(expression, key="grouping_sets")
2252        cube = self.expressions(expression, key="cube")
2253        rollup = self.expressions(expression, key="rollup")
2254
2255        groupings = csv(
2256            self.seg(grouping_sets) if grouping_sets else "",
2257            self.seg(cube) if cube else "",
2258            self.seg(rollup) if rollup else "",
2259            self.seg("WITH TOTALS") if expression.args.get("totals") else "",
2260            sep=self.GROUPINGS_SEP,
2261        )
2262
2263        if (
2264            expression.expressions
2265            and groupings
2266            and groupings.strip() not in ("WITH CUBE", "WITH ROLLUP")
2267        ):
2268            group_by = f"{group_by}{self.GROUPINGS_SEP}"
2269
2270        return f"{group_by}{groupings}"
2271
2272    def having_sql(self, expression: exp.Having) -> str:
2273        this = self.indent(self.sql(expression, "this"))
2274        return f"{self.seg('HAVING')}{self.sep()}{this}"
2275
2276    def connect_sql(self, expression: exp.Connect) -> str:
2277        start = self.sql(expression, "start")
2278        start = self.seg(f"START WITH {start}") if start else ""
2279        nocycle = " NOCYCLE" if expression.args.get("nocycle") else ""
2280        connect = self.sql(expression, "connect")
2281        connect = self.seg(f"CONNECT BY{nocycle} {connect}")
2282        return start + connect
2283
2284    def prior_sql(self, expression: exp.Prior) -> str:
2285        return f"PRIOR {self.sql(expression, 'this')}"
2286
2287    def join_sql(self, expression: exp.Join) -> str:
2288        if not self.SEMI_ANTI_JOIN_WITH_SIDE and expression.kind in ("SEMI", "ANTI"):
2289            side = None
2290        else:
2291            side = expression.side
2292
2293        op_sql = " ".join(
2294            op
2295            for op in (
2296                expression.method,
2297                "GLOBAL" if expression.args.get("global") else None,
2298                side,
2299                expression.kind,
2300                expression.hint if self.JOIN_HINTS else None,
2301            )
2302            if op
2303        )
2304        match_cond = self.sql(expression, "match_condition")
2305        match_cond = f" MATCH_CONDITION ({match_cond})" if match_cond else ""
2306        on_sql = self.sql(expression, "on")
2307        using = expression.args.get("using")
2308
2309        if not on_sql and using:
2310            on_sql = csv(*(self.sql(column) for column in using))
2311
2312        this = expression.this
2313        this_sql = self.sql(this)
2314
2315        exprs = self.expressions(expression)
2316        if exprs:
2317            this_sql = f"{this_sql},{self.seg(exprs)}"
2318
2319        if on_sql:
2320            on_sql = self.indent(on_sql, skip_first=True)
2321            space = self.seg(" " * self.pad) if self.pretty else " "
2322            if using:
2323                on_sql = f"{space}USING ({on_sql})"
2324            else:
2325                on_sql = f"{space}ON {on_sql}"
2326        elif not op_sql:
2327            if isinstance(this, exp.Lateral) and this.args.get("cross_apply") is not None:
2328                return f" {this_sql}"
2329
2330            return f", {this_sql}"
2331
2332        if op_sql != "STRAIGHT_JOIN":
2333            op_sql = f"{op_sql} JOIN" if op_sql else "JOIN"
2334
2335        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2336        return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}{pivots}"
2337
2338    def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->", wrap: bool = True) -> str:
2339        args = self.expressions(expression, flat=True)
2340        args = f"({args})" if wrap and len(args.split(",")) > 1 else args
2341        return f"{args} {arrow_sep} {self.sql(expression, 'this')}"
2342
2343    def lateral_op(self, expression: exp.Lateral) -> str:
2344        cross_apply = expression.args.get("cross_apply")
2345
2346        # https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
2347        if cross_apply is True:
2348            op = "INNER JOIN "
2349        elif cross_apply is False:
2350            op = "LEFT JOIN "
2351        else:
2352            op = ""
2353
2354        return f"{op}LATERAL"
2355
2356    def lateral_sql(self, expression: exp.Lateral) -> str:
2357        this = self.sql(expression, "this")
2358
2359        if expression.args.get("view"):
2360            alias = expression.args["alias"]
2361            columns = self.expressions(alias, key="columns", flat=True)
2362            table = f" {alias.name}" if alias.name else ""
2363            columns = f" AS {columns}" if columns else ""
2364            op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}")
2365            return f"{op_sql}{self.sep()}{this}{table}{columns}"
2366
2367        alias = self.sql(expression, "alias")
2368        alias = f" AS {alias}" if alias else ""
2369
2370        ordinality = expression.args.get("ordinality") or ""
2371        if ordinality:
2372            ordinality = f" WITH ORDINALITY{alias}"
2373            alias = ""
2374
2375        return f"{self.lateral_op(expression)} {this}{alias}{ordinality}"
2376
2377    def limit_sql(self, expression: exp.Limit, top: bool = False) -> str:
2378        this = self.sql(expression, "this")
2379
2380        args = [
2381            self._simplify_unless_literal(e) if self.LIMIT_ONLY_LITERALS else e
2382            for e in (expression.args.get(k) for k in ("offset", "expression"))
2383            if e
2384        ]
2385
2386        args_sql = ", ".join(self.sql(e) for e in args)
2387        args_sql = f"({args_sql})" if top and any(not e.is_number for e in args) else args_sql
2388        expressions = self.expressions(expression, flat=True)
2389        limit_options = self.sql(expression, "limit_options")
2390        expressions = f" BY {expressions}" if expressions else ""
2391
2392        return f"{this}{self.seg('TOP' if top else 'LIMIT')} {args_sql}{limit_options}{expressions}"
2393
2394    def offset_sql(self, expression: exp.Offset) -> str:
2395        this = self.sql(expression, "this")
2396        value = expression.expression
2397        value = self._simplify_unless_literal(value) if self.LIMIT_ONLY_LITERALS else value
2398        expressions = self.expressions(expression, flat=True)
2399        expressions = f" BY {expressions}" if expressions else ""
2400        return f"{this}{self.seg('OFFSET')} {self.sql(value)}{expressions}"
2401
2402    def setitem_sql(self, expression: exp.SetItem) -> str:
2403        kind = self.sql(expression, "kind")
2404        kind = f"{kind} " if kind else ""
2405        this = self.sql(expression, "this")
2406        expressions = self.expressions(expression)
2407        collate = self.sql(expression, "collate")
2408        collate = f" COLLATE {collate}" if collate else ""
2409        global_ = "GLOBAL " if expression.args.get("global") else ""
2410        return f"{global_}{kind}{this}{expressions}{collate}"
2411
2412    def set_sql(self, expression: exp.Set) -> str:
2413        expressions = f" {self.expressions(expression, flat=True)}"
2414        tag = " TAG" if expression.args.get("tag") else ""
2415        return f"{'UNSET' if expression.args.get('unset') else 'SET'}{tag}{expressions}"
2416
2417    def queryband_sql(self, expression: exp.QueryBand) -> str:
2418        this = self.sql(expression, "this")
2419        update = " UPDATE" if expression.args.get("update") else ""
2420        scope = self.sql(expression, "scope")
2421        scope = f" FOR {scope}" if scope else ""
2422
2423        return f"QUERY_BAND = {this}{update}{scope}"
2424
2425    def pragma_sql(self, expression: exp.Pragma) -> str:
2426        return f"PRAGMA {self.sql(expression, 'this')}"
2427
2428    def lock_sql(self, expression: exp.Lock) -> str:
2429        if not self.LOCKING_READS_SUPPORTED:
2430            self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported")
2431            return ""
2432
2433        update = expression.args["update"]
2434        key = expression.args.get("key")
2435        if update:
2436            lock_type = "FOR NO KEY UPDATE" if key else "FOR UPDATE"
2437        else:
2438            lock_type = "FOR KEY SHARE" if key else "FOR SHARE"
2439        expressions = self.expressions(expression, flat=True)
2440        expressions = f" OF {expressions}" if expressions else ""
2441        wait = expression.args.get("wait")
2442
2443        if wait is not None:
2444            if isinstance(wait, exp.Literal):
2445                wait = f" WAIT {self.sql(wait)}"
2446            else:
2447                wait = " NOWAIT" if wait else " SKIP LOCKED"
2448
2449        return f"{lock_type}{expressions}{wait or ''}"
2450
2451    def literal_sql(self, expression: exp.Literal) -> str:
2452        text = expression.this or ""
2453        if expression.is_string:
2454            text = f"{self.dialect.QUOTE_START}{self.escape_str(text)}{self.dialect.QUOTE_END}"
2455        return text
2456
2457    def escape_str(self, text: str, escape_backslash: bool = True) -> str:
2458        if self.dialect.ESCAPED_SEQUENCES:
2459            to_escaped = self.dialect.ESCAPED_SEQUENCES
2460            text = "".join(
2461                to_escaped.get(ch, ch) if escape_backslash or ch != "\\" else ch for ch in text
2462            )
2463
2464        return self._replace_line_breaks(text).replace(
2465            self.dialect.QUOTE_END, self._escaped_quote_end
2466        )
2467
2468    def loaddata_sql(self, expression: exp.LoadData) -> str:
2469        local = " LOCAL" if expression.args.get("local") else ""
2470        inpath = f" INPATH {self.sql(expression, 'inpath')}"
2471        overwrite = " OVERWRITE" if expression.args.get("overwrite") else ""
2472        this = f" INTO TABLE {self.sql(expression, 'this')}"
2473        partition = self.sql(expression, "partition")
2474        partition = f" {partition}" if partition else ""
2475        input_format = self.sql(expression, "input_format")
2476        input_format = f" INPUTFORMAT {input_format}" if input_format else ""
2477        serde = self.sql(expression, "serde")
2478        serde = f" SERDE {serde}" if serde else ""
2479        return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}"
2480
2481    def null_sql(self, *_) -> str:
2482        return "NULL"
2483
2484    def boolean_sql(self, expression: exp.Boolean) -> str:
2485        return "TRUE" if expression.this else "FALSE"
2486
2487    def order_sql(self, expression: exp.Order, flat: bool = False) -> str:
2488        this = self.sql(expression, "this")
2489        this = f"{this} " if this else this
2490        siblings = "SIBLINGS " if expression.args.get("siblings") else ""
2491        return self.op_expressions(f"{this}ORDER {siblings}BY", expression, flat=this or flat)  # type: ignore
2492
2493    def withfill_sql(self, expression: exp.WithFill) -> str:
2494        from_sql = self.sql(expression, "from")
2495        from_sql = f" FROM {from_sql}" if from_sql else ""
2496        to_sql = self.sql(expression, "to")
2497        to_sql = f" TO {to_sql}" if to_sql else ""
2498        step_sql = self.sql(expression, "step")
2499        step_sql = f" STEP {step_sql}" if step_sql else ""
2500        interpolated_values = [
2501            f"{self.sql(e, 'alias')} AS {self.sql(e, 'this')}"
2502            if isinstance(e, exp.Alias)
2503            else self.sql(e, "this")
2504            for e in expression.args.get("interpolate") or []
2505        ]
2506        interpolate = (
2507            f" INTERPOLATE ({', '.join(interpolated_values)})" if interpolated_values else ""
2508        )
2509        return f"WITH FILL{from_sql}{to_sql}{step_sql}{interpolate}"
2510
2511    def cluster_sql(self, expression: exp.Cluster) -> str:
2512        return self.op_expressions("CLUSTER BY", expression)
2513
2514    def distribute_sql(self, expression: exp.Distribute) -> str:
2515        return self.op_expressions("DISTRIBUTE BY", expression)
2516
2517    def sort_sql(self, expression: exp.Sort) -> str:
2518        return self.op_expressions("SORT BY", expression)
2519
2520    def ordered_sql(self, expression: exp.Ordered) -> str:
2521        desc = expression.args.get("desc")
2522        asc = not desc
2523
2524        nulls_first = expression.args.get("nulls_first")
2525        nulls_last = not nulls_first
2526        nulls_are_large = self.dialect.NULL_ORDERING == "nulls_are_large"
2527        nulls_are_small = self.dialect.NULL_ORDERING == "nulls_are_small"
2528        nulls_are_last = self.dialect.NULL_ORDERING == "nulls_are_last"
2529
2530        this = self.sql(expression, "this")
2531
2532        sort_order = " DESC" if desc else (" ASC" if desc is False else "")
2533        nulls_sort_change = ""
2534        if nulls_first and (
2535            (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last
2536        ):
2537            nulls_sort_change = " NULLS FIRST"
2538        elif (
2539            nulls_last
2540            and ((asc and nulls_are_small) or (desc and nulls_are_large))
2541            and not nulls_are_last
2542        ):
2543            nulls_sort_change = " NULLS LAST"
2544
2545        # If the NULLS FIRST/LAST clause is unsupported, we add another sort key to simulate it
2546        if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED:
2547            window = expression.find_ancestor(exp.Window, exp.Select)
2548            if isinstance(window, exp.Window) and window.args.get("spec"):
2549                self.unsupported(
2550                    f"'{nulls_sort_change.strip()}' translation not supported in window functions"
2551                )
2552                nulls_sort_change = ""
2553            elif self.NULL_ORDERING_SUPPORTED is False and (
2554                (asc and nulls_sort_change == " NULLS LAST")
2555                or (desc and nulls_sort_change == " NULLS FIRST")
2556            ):
2557                # BigQuery does not allow these ordering/nulls combinations when used under
2558                # an aggregation func or under a window containing one
2559                ancestor = expression.find_ancestor(exp.AggFunc, exp.Window, exp.Select)
2560
2561                if isinstance(ancestor, exp.Window):
2562                    ancestor = ancestor.this
2563                if isinstance(ancestor, exp.AggFunc):
2564                    self.unsupported(
2565                        f"'{nulls_sort_change.strip()}' translation not supported for aggregate functions with {sort_order} sort order"
2566                    )
2567                    nulls_sort_change = ""
2568            elif self.NULL_ORDERING_SUPPORTED is None:
2569                if expression.this.is_int:
2570                    self.unsupported(
2571                        f"'{nulls_sort_change.strip()}' translation not supported with positional ordering"
2572                    )
2573                elif not isinstance(expression.this, exp.Rand):
2574                    null_sort_order = " DESC" if nulls_sort_change == " NULLS FIRST" else ""
2575                    this = f"CASE WHEN {this} IS NULL THEN 1 ELSE 0 END{null_sort_order}, {this}"
2576                nulls_sort_change = ""
2577
2578        with_fill = self.sql(expression, "with_fill")
2579        with_fill = f" {with_fill}" if with_fill else ""
2580
2581        return f"{this}{sort_order}{nulls_sort_change}{with_fill}"
2582
2583    def matchrecognizemeasure_sql(self, expression: exp.MatchRecognizeMeasure) -> str:
2584        window_frame = self.sql(expression, "window_frame")
2585        window_frame = f"{window_frame} " if window_frame else ""
2586
2587        this = self.sql(expression, "this")
2588
2589        return f"{window_frame}{this}"
2590
2591    def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str:
2592        partition = self.partition_by_sql(expression)
2593        order = self.sql(expression, "order")
2594        measures = self.expressions(expression, key="measures")
2595        measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else ""
2596        rows = self.sql(expression, "rows")
2597        rows = self.seg(rows) if rows else ""
2598        after = self.sql(expression, "after")
2599        after = self.seg(after) if after else ""
2600        pattern = self.sql(expression, "pattern")
2601        pattern = self.seg(f"PATTERN ({pattern})") if pattern else ""
2602        definition_sqls = [
2603            f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}"
2604            for definition in expression.args.get("define", [])
2605        ]
2606        definitions = self.expressions(sqls=definition_sqls)
2607        define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else ""
2608        body = "".join(
2609            (
2610                partition,
2611                order,
2612                measures,
2613                rows,
2614                after,
2615                pattern,
2616                define,
2617            )
2618        )
2619        alias = self.sql(expression, "alias")
2620        alias = f" {alias}" if alias else ""
2621        return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}"
2622
2623    def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str:
2624        limit = expression.args.get("limit")
2625
2626        if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch):
2627            limit = exp.Limit(expression=exp.maybe_copy(limit.args.get("count")))
2628        elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit):
2629            limit = exp.Fetch(direction="FIRST", count=exp.maybe_copy(limit.expression))
2630
2631        return csv(
2632            *sqls,
2633            *[self.sql(join) for join in expression.args.get("joins") or []],
2634            self.sql(expression, "match"),
2635            *[self.sql(lateral) for lateral in expression.args.get("laterals") or []],
2636            self.sql(expression, "prewhere"),
2637            self.sql(expression, "where"),
2638            self.sql(expression, "connect"),
2639            self.sql(expression, "group"),
2640            self.sql(expression, "having"),
2641            *[gen(self, expression) for gen in self.AFTER_HAVING_MODIFIER_TRANSFORMS.values()],
2642            self.sql(expression, "order"),
2643            *self.offset_limit_modifiers(expression, isinstance(limit, exp.Fetch), limit),
2644            *self.after_limit_modifiers(expression),
2645            self.options_modifier(expression),
2646            self.for_modifiers(expression),
2647            sep="",
2648        )
2649
2650    def options_modifier(self, expression: exp.Expression) -> str:
2651        options = self.expressions(expression, key="options")
2652        return f" {options}" if options else ""
2653
2654    def for_modifiers(self, expression: exp.Expression) -> str:
2655        for_modifiers = self.expressions(expression, key="for")
2656        return f"{self.sep()}FOR XML{self.seg(for_modifiers)}" if for_modifiers else ""
2657
2658    def queryoption_sql(self, expression: exp.QueryOption) -> str:
2659        self.unsupported("Unsupported query option.")
2660        return ""
2661
2662    def offset_limit_modifiers(
2663        self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
2664    ) -> t.List[str]:
2665        return [
2666            self.sql(expression, "offset") if fetch else self.sql(limit),
2667            self.sql(limit) if fetch else self.sql(expression, "offset"),
2668        ]
2669
2670    def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
2671        locks = self.expressions(expression, key="locks", sep=" ")
2672        locks = f" {locks}" if locks else ""
2673        return [locks, self.sql(expression, "sample")]
2674
2675    def select_sql(self, expression: exp.Select) -> str:
2676        into = expression.args.get("into")
2677        if not self.SUPPORTS_SELECT_INTO and into:
2678            into.pop()
2679
2680        hint = self.sql(expression, "hint")
2681        distinct = self.sql(expression, "distinct")
2682        distinct = f" {distinct}" if distinct else ""
2683        kind = self.sql(expression, "kind")
2684
2685        limit = expression.args.get("limit")
2686        if isinstance(limit, exp.Limit) and self.LIMIT_IS_TOP:
2687            top = self.limit_sql(limit, top=True)
2688            limit.pop()
2689        else:
2690            top = ""
2691
2692        expressions = self.expressions(expression)
2693
2694        if kind:
2695            if kind in self.SELECT_KINDS:
2696                kind = f" AS {kind}"
2697            else:
2698                if kind == "STRUCT":
2699                    expressions = self.expressions(
2700                        sqls=[
2701                            self.sql(
2702                                exp.Struct(
2703                                    expressions=[
2704                                        exp.PropertyEQ(this=e.args.get("alias"), expression=e.this)
2705                                        if isinstance(e, exp.Alias)
2706                                        else e
2707                                        for e in expression.expressions
2708                                    ]
2709                                )
2710                            )
2711                        ]
2712                    )
2713                kind = ""
2714
2715        operation_modifiers = self.expressions(expression, key="operation_modifiers", sep=" ")
2716        operation_modifiers = f"{self.sep()}{operation_modifiers}" if operation_modifiers else ""
2717
2718        # We use LIMIT_IS_TOP as a proxy for whether DISTINCT should go first because tsql and Teradata
2719        # are the only dialects that use LIMIT_IS_TOP and both place DISTINCT first.
2720        top_distinct = f"{distinct}{hint}{top}" if self.LIMIT_IS_TOP else f"{top}{hint}{distinct}"
2721        expressions = f"{self.sep()}{expressions}" if expressions else expressions
2722        sql = self.query_modifiers(
2723            expression,
2724            f"SELECT{top_distinct}{operation_modifiers}{kind}{expressions}",
2725            self.sql(expression, "into", comment=False),
2726            self.sql(expression, "from", comment=False),
2727        )
2728
2729        # If both the CTE and SELECT clauses have comments, generate the latter earlier
2730        if expression.args.get("with"):
2731            sql = self.maybe_comment(sql, expression)
2732            expression.pop_comments()
2733
2734        sql = self.prepend_ctes(expression, sql)
2735
2736        if not self.SUPPORTS_SELECT_INTO and into:
2737            if into.args.get("temporary"):
2738                table_kind = " TEMPORARY"
2739            elif self.SUPPORTS_UNLOGGED_TABLES and into.args.get("unlogged"):
2740                table_kind = " UNLOGGED"
2741            else:
2742                table_kind = ""
2743            sql = f"CREATE{table_kind} TABLE {self.sql(into.this)} AS {sql}"
2744
2745        return sql
2746
2747    def schema_sql(self, expression: exp.Schema) -> str:
2748        this = self.sql(expression, "this")
2749        sql = self.schema_columns_sql(expression)
2750        return f"{this} {sql}" if this and sql else this or sql
2751
2752    def schema_columns_sql(self, expression: exp.Schema) -> str:
2753        if expression.expressions:
2754            return f"({self.sep('')}{self.expressions(expression)}{self.seg(')', sep='')}"
2755        return ""
2756
2757    def star_sql(self, expression: exp.Star) -> str:
2758        except_ = self.expressions(expression, key="except", flat=True)
2759        except_ = f"{self.seg(self.STAR_EXCEPT)} ({except_})" if except_ else ""
2760        replace = self.expressions(expression, key="replace", flat=True)
2761        replace = f"{self.seg('REPLACE')} ({replace})" if replace else ""
2762        rename = self.expressions(expression, key="rename", flat=True)
2763        rename = f"{self.seg('RENAME')} ({rename})" if rename else ""
2764        return f"*{except_}{replace}{rename}"
2765
2766    def parameter_sql(self, expression: exp.Parameter) -> str:
2767        this = self.sql(expression, "this")
2768        return f"{self.PARAMETER_TOKEN}{this}"
2769
2770    def sessionparameter_sql(self, expression: exp.SessionParameter) -> str:
2771        this = self.sql(expression, "this")
2772        kind = expression.text("kind")
2773        if kind:
2774            kind = f"{kind}."
2775        return f"@@{kind}{this}"
2776
2777    def placeholder_sql(self, expression: exp.Placeholder) -> str:
2778        return f"{self.NAMED_PLACEHOLDER_TOKEN}{expression.name}" if expression.this else "?"
2779
2780    def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str:
2781        alias = self.sql(expression, "alias")
2782        alias = f"{sep}{alias}" if alias else ""
2783        sample = self.sql(expression, "sample")
2784        if self.dialect.ALIAS_POST_TABLESAMPLE and sample:
2785            alias = f"{sample}{alias}"
2786
2787            # Set to None so it's not generated again by self.query_modifiers()
2788            expression.set("sample", None)
2789
2790        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2791        sql = self.query_modifiers(expression, self.wrap(expression), alias, pivots)
2792        return self.prepend_ctes(expression, sql)
2793
2794    def qualify_sql(self, expression: exp.Qualify) -> str:
2795        this = self.indent(self.sql(expression, "this"))
2796        return f"{self.seg('QUALIFY')}{self.sep()}{this}"
2797
2798    def unnest_sql(self, expression: exp.Unnest) -> str:
2799        args = self.expressions(expression, flat=True)
2800
2801        alias = expression.args.get("alias")
2802        offset = expression.args.get("offset")
2803
2804        if self.UNNEST_WITH_ORDINALITY:
2805            if alias and isinstance(offset, exp.Expression):
2806                alias.append("columns", offset)
2807
2808        if alias and self.dialect.UNNEST_COLUMN_ONLY:
2809            columns = alias.columns
2810            alias = self.sql(columns[0]) if columns else ""
2811        else:
2812            alias = self.sql(alias)
2813
2814        alias = f" AS {alias}" if alias else alias
2815        if self.UNNEST_WITH_ORDINALITY:
2816            suffix = f" WITH ORDINALITY{alias}" if offset else alias
2817        else:
2818            if isinstance(offset, exp.Expression):
2819                suffix = f"{alias} WITH OFFSET AS {self.sql(offset)}"
2820            elif offset:
2821                suffix = f"{alias} WITH OFFSET"
2822            else:
2823                suffix = alias
2824
2825        return f"UNNEST({args}){suffix}"
2826
2827    def prewhere_sql(self, expression: exp.PreWhere) -> str:
2828        return ""
2829
2830    def where_sql(self, expression: exp.Where) -> str:
2831        this = self.indent(self.sql(expression, "this"))
2832        return f"{self.seg('WHERE')}{self.sep()}{this}"
2833
2834    def window_sql(self, expression: exp.Window) -> str:
2835        this = self.sql(expression, "this")
2836        partition = self.partition_by_sql(expression)
2837        order = expression.args.get("order")
2838        order = self.order_sql(order, flat=True) if order else ""
2839        spec = self.sql(expression, "spec")
2840        alias = self.sql(expression, "alias")
2841        over = self.sql(expression, "over") or "OVER"
2842
2843        this = f"{this} {'AS' if expression.arg_key == 'windows' else over}"
2844
2845        first = expression.args.get("first")
2846        if first is None:
2847            first = ""
2848        else:
2849            first = "FIRST" if first else "LAST"
2850
2851        if not partition and not order and not spec and alias:
2852            return f"{this} {alias}"
2853
2854        args = self.format_args(
2855            *[arg for arg in (alias, first, partition, order, spec) if arg], sep=" "
2856        )
2857        return f"{this} ({args})"
2858
2859    def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str:
2860        partition = self.expressions(expression, key="partition_by", flat=True)
2861        return f"PARTITION BY {partition}" if partition else ""
2862
2863    def windowspec_sql(self, expression: exp.WindowSpec) -> str:
2864        kind = self.sql(expression, "kind")
2865        start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ")
2866        end = (
2867            csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ")
2868            or "CURRENT ROW"
2869        )
2870
2871        window_spec = f"{kind} BETWEEN {start} AND {end}"
2872
2873        exclude = self.sql(expression, "exclude")
2874        if exclude:
2875            if self.SUPPORTS_WINDOW_EXCLUDE:
2876                window_spec += f" EXCLUDE {exclude}"
2877            else:
2878                self.unsupported("EXCLUDE clause is not supported in the WINDOW clause")
2879
2880        return window_spec
2881
2882    def withingroup_sql(self, expression: exp.WithinGroup) -> str:
2883        this = self.sql(expression, "this")
2884        expression_sql = self.sql(expression, "expression")[1:]  # order has a leading space
2885        return f"{this} WITHIN GROUP ({expression_sql})"
2886
2887    def between_sql(self, expression: exp.Between) -> str:
2888        this = self.sql(expression, "this")
2889        low = self.sql(expression, "low")
2890        high = self.sql(expression, "high")
2891        symmetric = expression.args.get("symmetric")
2892
2893        if symmetric and not self.SUPPORTS_BETWEEN_FLAGS:
2894            return f"({this} BETWEEN {low} AND {high} OR {this} BETWEEN {high} AND {low})"
2895
2896        flag = (
2897            " SYMMETRIC"
2898            if symmetric
2899            else " ASYMMETRIC"
2900            if symmetric is False and self.SUPPORTS_BETWEEN_FLAGS
2901            else ""  # silently drop ASYMMETRIC – semantics identical
2902        )
2903        return f"{this} BETWEEN{flag} {low} AND {high}"
2904
2905    def bracket_offset_expressions(
2906        self, expression: exp.Bracket, index_offset: t.Optional[int] = None
2907    ) -> t.List[exp.Expression]:
2908        return apply_index_offset(
2909            expression.this,
2910            expression.expressions,
2911            (index_offset or self.dialect.INDEX_OFFSET) - expression.args.get("offset", 0),
2912            dialect=self.dialect,
2913        )
2914
2915    def bracket_sql(self, expression: exp.Bracket) -> str:
2916        expressions = self.bracket_offset_expressions(expression)
2917        expressions_sql = ", ".join(self.sql(e) for e in expressions)
2918        return f"{self.sql(expression, 'this')}[{expressions_sql}]"
2919
2920    def all_sql(self, expression: exp.All) -> str:
2921        this = self.sql(expression, "this")
2922        if not isinstance(expression.this, (exp.Tuple, exp.Paren)):
2923            this = self.wrap(this)
2924        return f"ALL {this}"
2925
2926    def any_sql(self, expression: exp.Any) -> str:
2927        this = self.sql(expression, "this")
2928        if isinstance(expression.this, (*exp.UNWRAPPED_QUERIES, exp.Paren)):
2929            if isinstance(expression.this, exp.UNWRAPPED_QUERIES):
2930                this = self.wrap(this)
2931            return f"ANY{this}"
2932        return f"ANY {this}"
2933
2934    def exists_sql(self, expression: exp.Exists) -> str:
2935        return f"EXISTS{self.wrap(expression)}"
2936
2937    def case_sql(self, expression: exp.Case) -> str:
2938        this = self.sql(expression, "this")
2939        statements = [f"CASE {this}" if this else "CASE"]
2940
2941        for e in expression.args["ifs"]:
2942            statements.append(f"WHEN {self.sql(e, 'this')}")
2943            statements.append(f"THEN {self.sql(e, 'true')}")
2944
2945        default = self.sql(expression, "default")
2946
2947        if default:
2948            statements.append(f"ELSE {default}")
2949
2950        statements.append("END")
2951
2952        if self.pretty and self.too_wide(statements):
2953            return self.indent("\n".join(statements), skip_first=True, skip_last=True)
2954
2955        return " ".join(statements)
2956
2957    def constraint_sql(self, expression: exp.Constraint) -> str:
2958        this = self.sql(expression, "this")
2959        expressions = self.expressions(expression, flat=True)
2960        return f"CONSTRAINT {this} {expressions}"
2961
2962    def nextvaluefor_sql(self, expression: exp.NextValueFor) -> str:
2963        order = expression.args.get("order")
2964        order = f" OVER ({self.order_sql(order, flat=True)})" if order else ""
2965        return f"NEXT VALUE FOR {self.sql(expression, 'this')}{order}"
2966
2967    def extract_sql(self, expression: exp.Extract) -> str:
2968        from sqlglot.dialects.dialect import map_date_part
2969
2970        this = (
2971            map_date_part(expression.this, self.dialect)
2972            if self.NORMALIZE_EXTRACT_DATE_PARTS
2973            else expression.this
2974        )
2975        this_sql = self.sql(this) if self.EXTRACT_ALLOWS_QUOTES else this.name
2976        expression_sql = self.sql(expression, "expression")
2977
2978        return f"EXTRACT({this_sql} FROM {expression_sql})"
2979
2980    def trim_sql(self, expression: exp.Trim) -> str:
2981        trim_type = self.sql(expression, "position")
2982
2983        if trim_type == "LEADING":
2984            func_name = "LTRIM"
2985        elif trim_type == "TRAILING":
2986            func_name = "RTRIM"
2987        else:
2988            func_name = "TRIM"
2989
2990        return self.func(func_name, expression.this, expression.expression)
2991
2992    def convert_concat_args(self, expression: exp.Concat | exp.ConcatWs) -> t.List[exp.Expression]:
2993        args = expression.expressions
2994        if isinstance(expression, exp.ConcatWs):
2995            args = args[1:]  # Skip the delimiter
2996
2997        if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
2998            args = [exp.cast(e, exp.DataType.Type.TEXT) for e in args]
2999
3000        if not self.dialect.CONCAT_COALESCE and expression.args.get("coalesce"):
3001
3002            def _wrap_with_coalesce(e: exp.Expression) -> exp.Expression:
3003                if not e.type:
3004                    from sqlglot.optimizer.annotate_types import annotate_types
3005
3006                    e = annotate_types(e, dialect=self.dialect)
3007
3008                if e.is_string or e.is_type(exp.DataType.Type.ARRAY):
3009                    return e
3010
3011                return exp.func("coalesce", e, exp.Literal.string(""))
3012
3013            args = [_wrap_with_coalesce(e) for e in args]
3014
3015        return args
3016
3017    def concat_sql(self, expression: exp.Concat) -> str:
3018        expressions = self.convert_concat_args(expression)
3019
3020        # Some dialects don't allow a single-argument CONCAT call
3021        if not self.SUPPORTS_SINGLE_ARG_CONCAT and len(expressions) == 1:
3022            return self.sql(expressions[0])
3023
3024        return self.func("CONCAT", *expressions)
3025
3026    def concatws_sql(self, expression: exp.ConcatWs) -> str:
3027        return self.func(
3028            "CONCAT_WS", seq_get(expression.expressions, 0), *self.convert_concat_args(expression)
3029        )
3030
3031    def check_sql(self, expression: exp.Check) -> str:
3032        this = self.sql(expression, key="this")
3033        return f"CHECK ({this})"
3034
3035    def foreignkey_sql(self, expression: exp.ForeignKey) -> str:
3036        expressions = self.expressions(expression, flat=True)
3037        expressions = f" ({expressions})" if expressions else ""
3038        reference = self.sql(expression, "reference")
3039        reference = f" {reference}" if reference else ""
3040        delete = self.sql(expression, "delete")
3041        delete = f" ON DELETE {delete}" if delete else ""
3042        update = self.sql(expression, "update")
3043        update = f" ON UPDATE {update}" if update else ""
3044        options = self.expressions(expression, key="options", flat=True, sep=" ")
3045        options = f" {options}" if options else ""
3046        return f"FOREIGN KEY{expressions}{reference}{delete}{update}{options}"
3047
3048    def primarykey_sql(self, expression: exp.PrimaryKey) -> str:
3049        expressions = self.expressions(expression, flat=True)
3050        include = self.sql(expression, "include")
3051        options = self.expressions(expression, key="options", flat=True, sep=" ")
3052        options = f" {options}" if options else ""
3053        return f"PRIMARY KEY ({expressions}){include}{options}"
3054
3055    def if_sql(self, expression: exp.If) -> str:
3056        return self.case_sql(exp.Case(ifs=[expression], default=expression.args.get("false")))
3057
3058    def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
3059        modifier = expression.args.get("modifier")
3060        modifier = f" {modifier}" if modifier else ""
3061        return f"{self.func('MATCH', *expression.expressions)} AGAINST({self.sql(expression, 'this')}{modifier})"
3062
3063    def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str:
3064        return f"{self.sql(expression, 'this')}{self.JSON_KEY_VALUE_PAIR_SEP} {self.sql(expression, 'expression')}"
3065
3066    def jsonpath_sql(self, expression: exp.JSONPath) -> str:
3067        path = self.expressions(expression, sep="", flat=True).lstrip(".")
3068
3069        if expression.args.get("escape"):
3070            path = self.escape_str(path)
3071
3072        if self.QUOTE_JSON_PATH:
3073            path = f"{self.dialect.QUOTE_START}{path}{self.dialect.QUOTE_END}"
3074
3075        return path
3076
3077    def json_path_part(self, expression: int | str | exp.JSONPathPart) -> str:
3078        if isinstance(expression, exp.JSONPathPart):
3079            transform = self.TRANSFORMS.get(expression.__class__)
3080            if not callable(transform):
3081                self.unsupported(f"Unsupported JSONPathPart type {expression.__class__.__name__}")
3082                return ""
3083
3084            return transform(self, expression)
3085
3086        if isinstance(expression, int):
3087            return str(expression)
3088
3089        if self._quote_json_path_key_using_brackets and self.JSON_PATH_SINGLE_QUOTE_ESCAPE:
3090            escaped = expression.replace("'", "\\'")
3091            escaped = f"\\'{expression}\\'"
3092        else:
3093            escaped = expression.replace('"', '\\"')
3094            escaped = f'"{escaped}"'
3095
3096        return escaped
3097
3098    def formatjson_sql(self, expression: exp.FormatJson) -> str:
3099        return f"{self.sql(expression, 'this')} FORMAT JSON"
3100
3101    def formatphrase_sql(self, expression: exp.FormatPhrase) -> str:
3102        # Output the Teradata column FORMAT override.
3103        # https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Data-Types-and-Literals/Data-Type-Formats-and-Format-Phrases/FORMAT
3104        this = self.sql(expression, "this")
3105        fmt = self.sql(expression, "format")
3106        return f"{this} (FORMAT {fmt})"
3107
3108    def jsonobject_sql(self, expression: exp.JSONObject | exp.JSONObjectAgg) -> str:
3109        null_handling = expression.args.get("null_handling")
3110        null_handling = f" {null_handling}" if null_handling else ""
3111
3112        unique_keys = expression.args.get("unique_keys")
3113        if unique_keys is not None:
3114            unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS"
3115        else:
3116            unique_keys = ""
3117
3118        return_type = self.sql(expression, "return_type")
3119        return_type = f" RETURNING {return_type}" if return_type else ""
3120        encoding = self.sql(expression, "encoding")
3121        encoding = f" ENCODING {encoding}" if encoding else ""
3122
3123        return self.func(
3124            "JSON_OBJECT" if isinstance(expression, exp.JSONObject) else "JSON_OBJECTAGG",
3125            *expression.expressions,
3126            suffix=f"{null_handling}{unique_keys}{return_type}{encoding})",
3127        )
3128
3129    def jsonobjectagg_sql(self, expression: exp.JSONObjectAgg) -> str:
3130        return self.jsonobject_sql(expression)
3131
3132    def jsonarray_sql(self, expression: exp.JSONArray) -> str:
3133        null_handling = expression.args.get("null_handling")
3134        null_handling = f" {null_handling}" if null_handling else ""
3135        return_type = self.sql(expression, "return_type")
3136        return_type = f" RETURNING {return_type}" if return_type else ""
3137        strict = " STRICT" if expression.args.get("strict") else ""
3138        return self.func(
3139            "JSON_ARRAY", *expression.expressions, suffix=f"{null_handling}{return_type}{strict})"
3140        )
3141
3142    def jsonarrayagg_sql(self, expression: exp.JSONArrayAgg) -> str:
3143        this = self.sql(expression, "this")
3144        order = self.sql(expression, "order")
3145        null_handling = expression.args.get("null_handling")
3146        null_handling = f" {null_handling}" if null_handling else ""
3147        return_type = self.sql(expression, "return_type")
3148        return_type = f" RETURNING {return_type}" if return_type else ""
3149        strict = " STRICT" if expression.args.get("strict") else ""
3150        return self.func(
3151            "JSON_ARRAYAGG",
3152            this,
3153            suffix=f"{order}{null_handling}{return_type}{strict})",
3154        )
3155
3156    def jsoncolumndef_sql(self, expression: exp.JSONColumnDef) -> str:
3157        path = self.sql(expression, "path")
3158        path = f" PATH {path}" if path else ""
3159        nested_schema = self.sql(expression, "nested_schema")
3160
3161        if nested_schema:
3162            return f"NESTED{path} {nested_schema}"
3163
3164        this = self.sql(expression, "this")
3165        kind = self.sql(expression, "kind")
3166        kind = f" {kind}" if kind else ""
3167        return f"{this}{kind}{path}"
3168
3169    def jsonschema_sql(self, expression: exp.JSONSchema) -> str:
3170        return self.func("COLUMNS", *expression.expressions)
3171
3172    def jsontable_sql(self, expression: exp.JSONTable) -> str:
3173        this = self.sql(expression, "this")
3174        path = self.sql(expression, "path")
3175        path = f", {path}" if path else ""
3176        error_handling = expression.args.get("error_handling")
3177        error_handling = f" {error_handling}" if error_handling else ""
3178        empty_handling = expression.args.get("empty_handling")
3179        empty_handling = f" {empty_handling}" if empty_handling else ""
3180        schema = self.sql(expression, "schema")
3181        return self.func(
3182            "JSON_TABLE", this, suffix=f"{path}{error_handling}{empty_handling} {schema})"
3183        )
3184
3185    def openjsoncolumndef_sql(self, expression: exp.OpenJSONColumnDef) -> str:
3186        this = self.sql(expression, "this")
3187        kind = self.sql(expression, "kind")
3188        path = self.sql(expression, "path")
3189        path = f" {path}" if path else ""
3190        as_json = " AS JSON" if expression.args.get("as_json") else ""
3191        return f"{this} {kind}{path}{as_json}"
3192
3193    def openjson_sql(self, expression: exp.OpenJSON) -> str:
3194        this = self.sql(expression, "this")
3195        path = self.sql(expression, "path")
3196        path = f", {path}" if path else ""
3197        expressions = self.expressions(expression)
3198        with_ = (
3199            f" WITH ({self.seg(self.indent(expressions), sep='')}{self.seg(')', sep='')}"
3200            if expressions
3201            else ""
3202        )
3203        return f"OPENJSON({this}{path}){with_}"
3204
3205    def in_sql(self, expression: exp.In) -> str:
3206        query = expression.args.get("query")
3207        unnest = expression.args.get("unnest")
3208        field = expression.args.get("field")
3209        is_global = " GLOBAL" if expression.args.get("is_global") else ""
3210
3211        if query:
3212            in_sql = self.sql(query)
3213        elif unnest:
3214            in_sql = self.in_unnest_op(unnest)
3215        elif field:
3216            in_sql = self.sql(field)
3217        else:
3218            in_sql = f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
3219
3220        return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}"
3221
3222    def in_unnest_op(self, unnest: exp.Unnest) -> str:
3223        return f"(SELECT {self.sql(unnest)})"
3224
3225    def interval_sql(self, expression: exp.Interval) -> str:
3226        unit = self.sql(expression, "unit")
3227        if not self.INTERVAL_ALLOWS_PLURAL_FORM:
3228            unit = self.TIME_PART_SINGULARS.get(unit, unit)
3229        unit = f" {unit}" if unit else ""
3230
3231        if self.SINGLE_STRING_INTERVAL:
3232            this = expression.this.name if expression.this else ""
3233            return f"INTERVAL '{this}{unit}'" if this else f"INTERVAL{unit}"
3234
3235        this = self.sql(expression, "this")
3236        if this:
3237            unwrapped = isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES)
3238            this = f" {this}" if unwrapped else f" ({this})"
3239
3240        return f"INTERVAL{this}{unit}"
3241
3242    def return_sql(self, expression: exp.Return) -> str:
3243        return f"RETURN {self.sql(expression, 'this')}"
3244
3245    def reference_sql(self, expression: exp.Reference) -> str:
3246        this = self.sql(expression, "this")
3247        expressions = self.expressions(expression, flat=True)
3248        expressions = f"({expressions})" if expressions else ""
3249        options = self.expressions(expression, key="options", flat=True, sep=" ")
3250        options = f" {options}" if options else ""
3251        return f"REFERENCES {this}{expressions}{options}"
3252
3253    def anonymous_sql(self, expression: exp.Anonymous) -> str:
3254        # We don't normalize qualified functions such as a.b.foo(), because they can be case-sensitive
3255        parent = expression.parent
3256        is_qualified = isinstance(parent, exp.Dot) and expression is parent.expression
3257        return self.func(
3258            self.sql(expression, "this"), *expression.expressions, normalize=not is_qualified
3259        )
3260
3261    def paren_sql(self, expression: exp.Paren) -> str:
3262        sql = self.seg(self.indent(self.sql(expression, "this")), sep="")
3263        return f"({sql}{self.seg(')', sep='')}"
3264
3265    def neg_sql(self, expression: exp.Neg) -> str:
3266        # This makes sure we don't convert "- - 5" to "--5", which is a comment
3267        this_sql = self.sql(expression, "this")
3268        sep = " " if this_sql[0] == "-" else ""
3269        return f"-{sep}{this_sql}"
3270
3271    def not_sql(self, expression: exp.Not) -> str:
3272        return f"NOT {self.sql(expression, 'this')}"
3273
3274    def alias_sql(self, expression: exp.Alias) -> str:
3275        alias = self.sql(expression, "alias")
3276        alias = f" AS {alias}" if alias else ""
3277        return f"{self.sql(expression, 'this')}{alias}"
3278
3279    def pivotalias_sql(self, expression: exp.PivotAlias) -> str:
3280        alias = expression.args["alias"]
3281
3282        parent = expression.parent
3283        pivot = parent and parent.parent
3284
3285        if isinstance(pivot, exp.Pivot) and pivot.unpivot:
3286            identifier_alias = isinstance(alias, exp.Identifier)
3287            literal_alias = isinstance(alias, exp.Literal)
3288
3289            if identifier_alias and not self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3290                alias.replace(exp.Literal.string(alias.output_name))
3291            elif not identifier_alias and literal_alias and self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3292                alias.replace(exp.to_identifier(alias.output_name))
3293
3294        return self.alias_sql(expression)
3295
3296    def aliases_sql(self, expression: exp.Aliases) -> str:
3297        return f"{self.sql(expression, 'this')} AS ({self.expressions(expression, flat=True)})"
3298
3299    def atindex_sql(self, expression: exp.AtTimeZone) -> str:
3300        this = self.sql(expression, "this")
3301        index = self.sql(expression, "expression")
3302        return f"{this} AT {index}"
3303
3304    def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
3305        this = self.sql(expression, "this")
3306        zone = self.sql(expression, "zone")
3307        return f"{this} AT TIME ZONE {zone}"
3308
3309    def fromtimezone_sql(self, expression: exp.FromTimeZone) -> str:
3310        this = self.sql(expression, "this")
3311        zone = self.sql(expression, "zone")
3312        return f"{this} AT TIME ZONE {zone} AT TIME ZONE 'UTC'"
3313
3314    def add_sql(self, expression: exp.Add) -> str:
3315        return self.binary(expression, "+")
3316
3317    def and_sql(
3318        self, expression: exp.And, stack: t.Optional[t.List[str | exp.Expression]] = None
3319    ) -> str:
3320        return self.connector_sql(expression, "AND", stack)
3321
3322    def or_sql(
3323        self, expression: exp.Or, stack: t.Optional[t.List[str | exp.Expression]] = None
3324    ) -> str:
3325        return self.connector_sql(expression, "OR", stack)
3326
3327    def xor_sql(
3328        self, expression: exp.Xor, stack: t.Optional[t.List[str | exp.Expression]] = None
3329    ) -> str:
3330        return self.connector_sql(expression, "XOR", stack)
3331
3332    def connector_sql(
3333        self,
3334        expression: exp.Connector,
3335        op: str,
3336        stack: t.Optional[t.List[str | exp.Expression]] = None,
3337    ) -> str:
3338        if stack is not None:
3339            if expression.expressions:
3340                stack.append(self.expressions(expression, sep=f" {op} "))
3341            else:
3342                stack.append(expression.right)
3343                if expression.comments and self.comments:
3344                    for comment in expression.comments:
3345                        if comment:
3346                            op += f" /*{self.sanitize_comment(comment)}*/"
3347                stack.extend((op, expression.left))
3348            return op
3349
3350        stack = [expression]
3351        sqls: t.List[str] = []
3352        ops = set()
3353
3354        while stack:
3355            node = stack.pop()
3356            if isinstance(node, exp.Connector):
3357                ops.add(getattr(self, f"{node.key}_sql")(node, stack))
3358            else:
3359                sql = self.sql(node)
3360                if sqls and sqls[-1] in ops:
3361                    sqls[-1] += f" {sql}"
3362                else:
3363                    sqls.append(sql)
3364
3365        sep = "\n" if self.pretty and self.too_wide(sqls) else " "
3366        return sep.join(sqls)
3367
3368    def bitwiseand_sql(self, expression: exp.BitwiseAnd) -> str:
3369        return self.binary(expression, "&")
3370
3371    def bitwiseleftshift_sql(self, expression: exp.BitwiseLeftShift) -> str:
3372        return self.binary(expression, "<<")
3373
3374    def bitwisenot_sql(self, expression: exp.BitwiseNot) -> str:
3375        return f"~{self.sql(expression, 'this')}"
3376
3377    def bitwiseor_sql(self, expression: exp.BitwiseOr) -> str:
3378        return self.binary(expression, "|")
3379
3380    def bitwiserightshift_sql(self, expression: exp.BitwiseRightShift) -> str:
3381        return self.binary(expression, ">>")
3382
3383    def bitwisexor_sql(self, expression: exp.BitwiseXor) -> str:
3384        return self.binary(expression, "^")
3385
3386    def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
3387        format_sql = self.sql(expression, "format")
3388        format_sql = f" FORMAT {format_sql}" if format_sql else ""
3389        to_sql = self.sql(expression, "to")
3390        to_sql = f" {to_sql}" if to_sql else ""
3391        action = self.sql(expression, "action")
3392        action = f" {action}" if action else ""
3393        default = self.sql(expression, "default")
3394        default = f" DEFAULT {default} ON CONVERSION ERROR" if default else ""
3395        return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS{to_sql}{default}{format_sql}{action})"
3396
3397    def currentdate_sql(self, expression: exp.CurrentDate) -> str:
3398        zone = self.sql(expression, "this")
3399        return f"CURRENT_DATE({zone})" if zone else "CURRENT_DATE"
3400
3401    def collate_sql(self, expression: exp.Collate) -> str:
3402        if self.COLLATE_IS_FUNC:
3403            return self.function_fallback_sql(expression)
3404        return self.binary(expression, "COLLATE")
3405
3406    def command_sql(self, expression: exp.Command) -> str:
3407        return f"{self.sql(expression, 'this')} {expression.text('expression').strip()}"
3408
3409    def comment_sql(self, expression: exp.Comment) -> str:
3410        this = self.sql(expression, "this")
3411        kind = expression.args["kind"]
3412        materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
3413        exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
3414        expression_sql = self.sql(expression, "expression")
3415        return f"COMMENT{exists_sql}ON{materialized} {kind} {this} IS {expression_sql}"
3416
3417    def mergetreettlaction_sql(self, expression: exp.MergeTreeTTLAction) -> str:
3418        this = self.sql(expression, "this")
3419        delete = " DELETE" if expression.args.get("delete") else ""
3420        recompress = self.sql(expression, "recompress")
3421        recompress = f" RECOMPRESS {recompress}" if recompress else ""
3422        to_disk = self.sql(expression, "to_disk")
3423        to_disk = f" TO DISK {to_disk}" if to_disk else ""
3424        to_volume = self.sql(expression, "to_volume")
3425        to_volume = f" TO VOLUME {to_volume}" if to_volume else ""
3426        return f"{this}{delete}{recompress}{to_disk}{to_volume}"
3427
3428    def mergetreettl_sql(self, expression: exp.MergeTreeTTL) -> str:
3429        where = self.sql(expression, "where")
3430        group = self.sql(expression, "group")
3431        aggregates = self.expressions(expression, key="aggregates")
3432        aggregates = self.seg("SET") + self.seg(aggregates) if aggregates else ""
3433
3434        if not (where or group or aggregates) and len(expression.expressions) == 1:
3435            return f"TTL {self.expressions(expression, flat=True)}"
3436
3437        return f"TTL{self.seg(self.expressions(expression))}{where}{group}{aggregates}"
3438
3439    def transaction_sql(self, expression: exp.Transaction) -> str:
3440        return "BEGIN"
3441
3442    def commit_sql(self, expression: exp.Commit) -> str:
3443        chain = expression.args.get("chain")
3444        if chain is not None:
3445            chain = " AND CHAIN" if chain else " AND NO CHAIN"
3446
3447        return f"COMMIT{chain or ''}"
3448
3449    def rollback_sql(self, expression: exp.Rollback) -> str:
3450        savepoint = expression.args.get("savepoint")
3451        savepoint = f" TO {savepoint}" if savepoint else ""
3452        return f"ROLLBACK{savepoint}"
3453
3454    def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
3455        this = self.sql(expression, "this")
3456
3457        dtype = self.sql(expression, "dtype")
3458        if dtype:
3459            collate = self.sql(expression, "collate")
3460            collate = f" COLLATE {collate}" if collate else ""
3461            using = self.sql(expression, "using")
3462            using = f" USING {using}" if using else ""
3463            alter_set_type = self.ALTER_SET_TYPE + " " if self.ALTER_SET_TYPE else ""
3464            return f"ALTER COLUMN {this} {alter_set_type}{dtype}{collate}{using}"
3465
3466        default = self.sql(expression, "default")
3467        if default:
3468            return f"ALTER COLUMN {this} SET DEFAULT {default}"
3469
3470        comment = self.sql(expression, "comment")
3471        if comment:
3472            return f"ALTER COLUMN {this} COMMENT {comment}"
3473
3474        visible = expression.args.get("visible")
3475        if visible:
3476            return f"ALTER COLUMN {this} SET {visible}"
3477
3478        allow_null = expression.args.get("allow_null")
3479        drop = expression.args.get("drop")
3480
3481        if not drop and not allow_null:
3482            self.unsupported("Unsupported ALTER COLUMN syntax")
3483
3484        if allow_null is not None:
3485            keyword = "DROP" if drop else "SET"
3486            return f"ALTER COLUMN {this} {keyword} NOT NULL"
3487
3488        return f"ALTER COLUMN {this} DROP DEFAULT"
3489
3490    def alterindex_sql(self, expression: exp.AlterIndex) -> str:
3491        this = self.sql(expression, "this")
3492
3493        visible = expression.args.get("visible")
3494        visible_sql = "VISIBLE" if visible else "INVISIBLE"
3495
3496        return f"ALTER INDEX {this} {visible_sql}"
3497
3498    def alterdiststyle_sql(self, expression: exp.AlterDistStyle) -> str:
3499        this = self.sql(expression, "this")
3500        if not isinstance(expression.this, exp.Var):
3501            this = f"KEY DISTKEY {this}"
3502        return f"ALTER DISTSTYLE {this}"
3503
3504    def altersortkey_sql(self, expression: exp.AlterSortKey) -> str:
3505        compound = " COMPOUND" if expression.args.get("compound") else ""
3506        this = self.sql(expression, "this")
3507        expressions = self.expressions(expression, flat=True)
3508        expressions = f"({expressions})" if expressions else ""
3509        return f"ALTER{compound} SORTKEY {this or expressions}"
3510
3511    def alterrename_sql(self, expression: exp.AlterRename, include_to: bool = True) -> str:
3512        if not self.RENAME_TABLE_WITH_DB:
3513            # Remove db from tables
3514            expression = expression.transform(
3515                lambda n: exp.table_(n.this) if isinstance(n, exp.Table) else n
3516            ).assert_is(exp.AlterRename)
3517        this = self.sql(expression, "this")
3518        to_kw = " TO" if include_to else ""
3519        return f"RENAME{to_kw} {this}"
3520
3521    def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
3522        exists = " IF EXISTS" if expression.args.get("exists") else ""
3523        old_column = self.sql(expression, "this")
3524        new_column = self.sql(expression, "to")
3525        return f"RENAME COLUMN{exists} {old_column} TO {new_column}"
3526
3527    def alterset_sql(self, expression: exp.AlterSet) -> str:
3528        exprs = self.expressions(expression, flat=True)
3529        if self.ALTER_SET_WRAPPED:
3530            exprs = f"({exprs})"
3531
3532        return f"SET {exprs}"
3533
3534    def alter_sql(self, expression: exp.Alter) -> str:
3535        actions = expression.args["actions"]
3536
3537        if not self.dialect.ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN and isinstance(
3538            actions[0], exp.ColumnDef
3539        ):
3540            actions_sql = self.expressions(expression, key="actions", flat=True)
3541            actions_sql = f"ADD {actions_sql}"
3542        else:
3543            actions_list = []
3544            for action in actions:
3545                if isinstance(action, (exp.ColumnDef, exp.Schema)):
3546                    action_sql = self.add_column_sql(action)
3547                else:
3548                    action_sql = self.sql(action)
3549                    if isinstance(action, exp.Query):
3550                        action_sql = f"AS {action_sql}"
3551
3552                actions_list.append(action_sql)
3553
3554            actions_sql = self.format_args(*actions_list).lstrip("\n")
3555
3556        exists = " IF EXISTS" if expression.args.get("exists") else ""
3557        on_cluster = self.sql(expression, "cluster")
3558        on_cluster = f" {on_cluster}" if on_cluster else ""
3559        only = " ONLY" if expression.args.get("only") else ""
3560        options = self.expressions(expression, key="options")
3561        options = f", {options}" if options else ""
3562        kind = self.sql(expression, "kind")
3563        not_valid = " NOT VALID" if expression.args.get("not_valid") else ""
3564        check = " WITH CHECK" if expression.args.get("check") else ""
3565
3566        return f"ALTER {kind}{exists}{only} {self.sql(expression, 'this')}{on_cluster}{check}{self.sep()}{actions_sql}{not_valid}{options}"
3567
3568    def add_column_sql(self, expression: exp.Expression) -> str:
3569        sql = self.sql(expression)
3570        if isinstance(expression, exp.Schema):
3571            column_text = " COLUMNS"
3572        elif isinstance(expression, exp.ColumnDef) and self.ALTER_TABLE_INCLUDE_COLUMN_KEYWORD:
3573            column_text = " COLUMN"
3574        else:
3575            column_text = ""
3576
3577        return f"ADD{column_text} {sql}"
3578
3579    def droppartition_sql(self, expression: exp.DropPartition) -> str:
3580        expressions = self.expressions(expression)
3581        exists = " IF EXISTS " if expression.args.get("exists") else " "
3582        return f"DROP{exists}{expressions}"
3583
3584    def addconstraint_sql(self, expression: exp.AddConstraint) -> str:
3585        return f"ADD {self.expressions(expression, indent=False)}"
3586
3587    def addpartition_sql(self, expression: exp.AddPartition) -> str:
3588        exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
3589        location = self.sql(expression, "location")
3590        location = f" {location}" if location else ""
3591        return f"ADD {exists}{self.sql(expression.this)}{location}"
3592
3593    def distinct_sql(self, expression: exp.Distinct) -> str:
3594        this = self.expressions(expression, flat=True)
3595
3596        if not self.MULTI_ARG_DISTINCT and len(expression.expressions) > 1:
3597            case = exp.case()
3598            for arg in expression.expressions:
3599                case = case.when(arg.is_(exp.null()), exp.null())
3600            this = self.sql(case.else_(f"({this})"))
3601
3602        this = f" {this}" if this else ""
3603
3604        on = self.sql(expression, "on")
3605        on = f" ON {on}" if on else ""
3606        return f"DISTINCT{this}{on}"
3607
3608    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
3609        return self._embed_ignore_nulls(expression, "IGNORE NULLS")
3610
3611    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
3612        return self._embed_ignore_nulls(expression, "RESPECT NULLS")
3613
3614    def havingmax_sql(self, expression: exp.HavingMax) -> str:
3615        this_sql = self.sql(expression, "this")
3616        expression_sql = self.sql(expression, "expression")
3617        kind = "MAX" if expression.args.get("max") else "MIN"
3618        return f"{this_sql} HAVING {kind} {expression_sql}"
3619
3620    def intdiv_sql(self, expression: exp.IntDiv) -> str:
3621        return self.sql(
3622            exp.Cast(
3623                this=exp.Div(this=expression.this, expression=expression.expression),
3624                to=exp.DataType(this=exp.DataType.Type.INT),
3625            )
3626        )
3627
3628    def dpipe_sql(self, expression: exp.DPipe) -> str:
3629        if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
3630            return self.func(
3631                "CONCAT", *(exp.cast(e, exp.DataType.Type.TEXT) for e in expression.flatten())
3632            )
3633        return self.binary(expression, "||")
3634
3635    def div_sql(self, expression: exp.Div) -> str:
3636        l, r = expression.left, expression.right
3637
3638        if not self.dialect.SAFE_DIVISION and expression.args.get("safe"):
3639            r.replace(exp.Nullif(this=r.copy(), expression=exp.Literal.number(0)))
3640
3641        if self.dialect.TYPED_DIVISION and not expression.args.get("typed"):
3642            if not l.is_type(*exp.DataType.REAL_TYPES) and not r.is_type(*exp.DataType.REAL_TYPES):
3643                l.replace(exp.cast(l.copy(), to=exp.DataType.Type.DOUBLE))
3644
3645        elif not self.dialect.TYPED_DIVISION and expression.args.get("typed"):
3646            if l.is_type(*exp.DataType.INTEGER_TYPES) and r.is_type(*exp.DataType.INTEGER_TYPES):
3647                return self.sql(
3648                    exp.cast(
3649                        l / r,
3650                        to=exp.DataType.Type.BIGINT,
3651                    )
3652                )
3653
3654        return self.binary(expression, "/")
3655
3656    def safedivide_sql(self, expression: exp.SafeDivide) -> str:
3657        n = exp._wrap(expression.this, exp.Binary)
3658        d = exp._wrap(expression.expression, exp.Binary)
3659        return self.sql(exp.If(this=d.neq(0), true=n / d, false=exp.Null()))
3660
3661    def overlaps_sql(self, expression: exp.Overlaps) -> str:
3662        return self.binary(expression, "OVERLAPS")
3663
3664    def distance_sql(self, expression: exp.Distance) -> str:
3665        return self.binary(expression, "<->")
3666
3667    def dot_sql(self, expression: exp.Dot) -> str:
3668        return f"{self.sql(expression, 'this')}.{self.sql(expression, 'expression')}"
3669
3670    def eq_sql(self, expression: exp.EQ) -> str:
3671        return self.binary(expression, "=")
3672
3673    def propertyeq_sql(self, expression: exp.PropertyEQ) -> str:
3674        return self.binary(expression, ":=")
3675
3676    def escape_sql(self, expression: exp.Escape) -> str:
3677        return self.binary(expression, "ESCAPE")
3678
3679    def glob_sql(self, expression: exp.Glob) -> str:
3680        return self.binary(expression, "GLOB")
3681
3682    def gt_sql(self, expression: exp.GT) -> str:
3683        return self.binary(expression, ">")
3684
3685    def gte_sql(self, expression: exp.GTE) -> str:
3686        return self.binary(expression, ">=")
3687
3688    def is_sql(self, expression: exp.Is) -> str:
3689        if not self.IS_BOOL_ALLOWED and isinstance(expression.expression, exp.Boolean):
3690            return self.sql(
3691                expression.this if expression.expression.this else exp.not_(expression.this)
3692            )
3693        return self.binary(expression, "IS")
3694
3695    def _like_sql(self, expression: exp.Like | exp.ILike) -> str:
3696        this = expression.this
3697        rhs = expression.expression
3698
3699        if isinstance(expression, exp.Like):
3700            exp_class: t.Type[exp.Like | exp.ILike] = exp.Like
3701            op = "LIKE"
3702        else:
3703            exp_class = exp.ILike
3704            op = "ILIKE"
3705
3706        if isinstance(rhs, (exp.All, exp.Any)) and not self.SUPPORTS_LIKE_QUANTIFIERS:
3707            exprs = rhs.this.unnest()
3708
3709            if isinstance(exprs, exp.Tuple):
3710                exprs = exprs.expressions
3711
3712            connective = exp.or_ if isinstance(rhs, exp.Any) else exp.and_
3713
3714            like_expr: exp.Expression = exp_class(this=this, expression=exprs[0])
3715            for expr in exprs[1:]:
3716                like_expr = connective(like_expr, exp_class(this=this, expression=expr))
3717
3718            return self.sql(like_expr)
3719
3720        return self.binary(expression, op)
3721
3722    def like_sql(self, expression: exp.Like) -> str:
3723        return self._like_sql(expression)
3724
3725    def ilike_sql(self, expression: exp.ILike) -> str:
3726        return self._like_sql(expression)
3727
3728    def similarto_sql(self, expression: exp.SimilarTo) -> str:
3729        return self.binary(expression, "SIMILAR TO")
3730
3731    def lt_sql(self, expression: exp.LT) -> str:
3732        return self.binary(expression, "<")
3733
3734    def lte_sql(self, expression: exp.LTE) -> str:
3735        return self.binary(expression, "<=")
3736
3737    def mod_sql(self, expression: exp.Mod) -> str:
3738        return self.binary(expression, "%")
3739
3740    def mul_sql(self, expression: exp.Mul) -> str:
3741        return self.binary(expression, "*")
3742
3743    def neq_sql(self, expression: exp.NEQ) -> str:
3744        return self.binary(expression, "<>")
3745
3746    def nullsafeeq_sql(self, expression: exp.NullSafeEQ) -> str:
3747        return self.binary(expression, "IS NOT DISTINCT FROM")
3748
3749    def nullsafeneq_sql(self, expression: exp.NullSafeNEQ) -> str:
3750        return self.binary(expression, "IS DISTINCT FROM")
3751
3752    def slice_sql(self, expression: exp.Slice) -> str:
3753        return self.binary(expression, ":")
3754
3755    def sub_sql(self, expression: exp.Sub) -> str:
3756        return self.binary(expression, "-")
3757
3758    def trycast_sql(self, expression: exp.TryCast) -> str:
3759        return self.cast_sql(expression, safe_prefix="TRY_")
3760
3761    def jsoncast_sql(self, expression: exp.JSONCast) -> str:
3762        return self.cast_sql(expression)
3763
3764    def try_sql(self, expression: exp.Try) -> str:
3765        if not self.TRY_SUPPORTED:
3766            self.unsupported("Unsupported TRY function")
3767            return self.sql(expression, "this")
3768
3769        return self.func("TRY", expression.this)
3770
3771    def log_sql(self, expression: exp.Log) -> str:
3772        this = expression.this
3773        expr = expression.expression
3774
3775        if self.dialect.LOG_BASE_FIRST is False:
3776            this, expr = expr, this
3777        elif self.dialect.LOG_BASE_FIRST is None and expr:
3778            if this.name in ("2", "10"):
3779                return self.func(f"LOG{this.name}", expr)
3780
3781            self.unsupported(f"Unsupported logarithm with base {self.sql(this)}")
3782
3783        return self.func("LOG", this, expr)
3784
3785    def use_sql(self, expression: exp.Use) -> str:
3786        kind = self.sql(expression, "kind")
3787        kind = f" {kind}" if kind else ""
3788        this = self.sql(expression, "this") or self.expressions(expression, flat=True)
3789        this = f" {this}" if this else ""
3790        return f"USE{kind}{this}"
3791
3792    def binary(self, expression: exp.Binary, op: str) -> str:
3793        sqls: t.List[str] = []
3794        stack: t.List[t.Union[str, exp.Expression]] = [expression]
3795        binary_type = type(expression)
3796
3797        while stack:
3798            node = stack.pop()
3799
3800            if type(node) is binary_type:
3801                op_func = node.args.get("operator")
3802                if op_func:
3803                    op = f"OPERATOR({self.sql(op_func)})"
3804
3805                stack.append(node.right)
3806                stack.append(f" {self.maybe_comment(op, comments=node.comments)} ")
3807                stack.append(node.left)
3808            else:
3809                sqls.append(self.sql(node))
3810
3811        return "".join(sqls)
3812
3813    def ceil_floor(self, expression: exp.Ceil | exp.Floor) -> str:
3814        to_clause = self.sql(expression, "to")
3815        if to_clause:
3816            return f"{expression.sql_name()}({self.sql(expression, 'this')} TO {to_clause})"
3817
3818        return self.function_fallback_sql(expression)
3819
3820    def function_fallback_sql(self, expression: exp.Func) -> str:
3821        args = []
3822
3823        for key in expression.arg_types:
3824            arg_value = expression.args.get(key)
3825
3826            if isinstance(arg_value, list):
3827                for value in arg_value:
3828                    args.append(value)
3829            elif arg_value is not None:
3830                args.append(arg_value)
3831
3832        if self.dialect.PRESERVE_ORIGINAL_NAMES:
3833            name = (expression._meta and expression.meta.get("name")) or expression.sql_name()
3834        else:
3835            name = expression.sql_name()
3836
3837        return self.func(name, *args)
3838
3839    def func(
3840        self,
3841        name: str,
3842        *args: t.Optional[exp.Expression | str],
3843        prefix: str = "(",
3844        suffix: str = ")",
3845        normalize: bool = True,
3846    ) -> str:
3847        name = self.normalize_func(name) if normalize else name
3848        return f"{name}{prefix}{self.format_args(*args)}{suffix}"
3849
3850    def format_args(self, *args: t.Optional[str | exp.Expression], sep: str = ", ") -> str:
3851        arg_sqls = tuple(
3852            self.sql(arg) for arg in args if arg is not None and not isinstance(arg, bool)
3853        )
3854        if self.pretty and self.too_wide(arg_sqls):
3855            return self.indent(
3856                "\n" + f"{sep.strip()}\n".join(arg_sqls) + "\n", skip_first=True, skip_last=True
3857            )
3858        return sep.join(arg_sqls)
3859
3860    def too_wide(self, args: t.Iterable) -> bool:
3861        return sum(len(arg) for arg in args) > self.max_text_width
3862
3863    def format_time(
3864        self,
3865        expression: exp.Expression,
3866        inverse_time_mapping: t.Optional[t.Dict[str, str]] = None,
3867        inverse_time_trie: t.Optional[t.Dict] = None,
3868    ) -> t.Optional[str]:
3869        return format_time(
3870            self.sql(expression, "format"),
3871            inverse_time_mapping or self.dialect.INVERSE_TIME_MAPPING,
3872            inverse_time_trie or self.dialect.INVERSE_TIME_TRIE,
3873        )
3874
3875    def expressions(
3876        self,
3877        expression: t.Optional[exp.Expression] = None,
3878        key: t.Optional[str] = None,
3879        sqls: t.Optional[t.Collection[str | exp.Expression]] = None,
3880        flat: bool = False,
3881        indent: bool = True,
3882        skip_first: bool = False,
3883        skip_last: bool = False,
3884        sep: str = ", ",
3885        prefix: str = "",
3886        dynamic: bool = False,
3887        new_line: bool = False,
3888    ) -> str:
3889        expressions = expression.args.get(key or "expressions") if expression else sqls
3890
3891        if not expressions:
3892            return ""
3893
3894        if flat:
3895            return sep.join(sql for sql in (self.sql(e) for e in expressions) if sql)
3896
3897        num_sqls = len(expressions)
3898        result_sqls = []
3899
3900        for i, e in enumerate(expressions):
3901            sql = self.sql(e, comment=False)
3902            if not sql:
3903                continue
3904
3905            comments = self.maybe_comment("", e) if isinstance(e, exp.Expression) else ""
3906
3907            if self.pretty:
3908                if self.leading_comma:
3909                    result_sqls.append(f"{sep if i > 0 else ''}{prefix}{sql}{comments}")
3910                else:
3911                    result_sqls.append(
3912                        f"{prefix}{sql}{(sep.rstrip() if comments else sep) if i + 1 < num_sqls else ''}{comments}"
3913                    )
3914            else:
3915                result_sqls.append(f"{prefix}{sql}{comments}{sep if i + 1 < num_sqls else ''}")
3916
3917        if self.pretty and (not dynamic or self.too_wide(result_sqls)):
3918            if new_line:
3919                result_sqls.insert(0, "")
3920                result_sqls.append("")
3921            result_sql = "\n".join(s.rstrip() for s in result_sqls)
3922        else:
3923            result_sql = "".join(result_sqls)
3924
3925        return (
3926            self.indent(result_sql, skip_first=skip_first, skip_last=skip_last)
3927            if indent
3928            else result_sql
3929        )
3930
3931    def op_expressions(self, op: str, expression: exp.Expression, flat: bool = False) -> str:
3932        flat = flat or isinstance(expression.parent, exp.Properties)
3933        expressions_sql = self.expressions(expression, flat=flat)
3934        if flat:
3935            return f"{op} {expressions_sql}"
3936        return f"{self.seg(op)}{self.sep() if expressions_sql else ''}{expressions_sql}"
3937
3938    def naked_property(self, expression: exp.Property) -> str:
3939        property_name = exp.Properties.PROPERTY_TO_NAME.get(expression.__class__)
3940        if not property_name:
3941            self.unsupported(f"Unsupported property {expression.__class__.__name__}")
3942        return f"{property_name} {self.sql(expression, 'this')}"
3943
3944    def tag_sql(self, expression: exp.Tag) -> str:
3945        return f"{expression.args.get('prefix')}{self.sql(expression.this)}{expression.args.get('postfix')}"
3946
3947    def token_sql(self, token_type: TokenType) -> str:
3948        return self.TOKEN_MAPPING.get(token_type, token_type.name)
3949
3950    def userdefinedfunction_sql(self, expression: exp.UserDefinedFunction) -> str:
3951        this = self.sql(expression, "this")
3952        expressions = self.no_identify(self.expressions, expression)
3953        expressions = (
3954            self.wrap(expressions) if expression.args.get("wrapped") else f" {expressions}"
3955        )
3956        return f"{this}{expressions}" if expressions.strip() != "" else this
3957
3958    def joinhint_sql(self, expression: exp.JoinHint) -> str:
3959        this = self.sql(expression, "this")
3960        expressions = self.expressions(expression, flat=True)
3961        return f"{this}({expressions})"
3962
3963    def kwarg_sql(self, expression: exp.Kwarg) -> str:
3964        return self.binary(expression, "=>")
3965
3966    def when_sql(self, expression: exp.When) -> str:
3967        matched = "MATCHED" if expression.args["matched"] else "NOT MATCHED"
3968        source = " BY SOURCE" if self.MATCHED_BY_SOURCE and expression.args.get("source") else ""
3969        condition = self.sql(expression, "condition")
3970        condition = f" AND {condition}" if condition else ""
3971
3972        then_expression = expression.args.get("then")
3973        if isinstance(then_expression, exp.Insert):
3974            this = self.sql(then_expression, "this")
3975            this = f"INSERT {this}" if this else "INSERT"
3976            then = self.sql(then_expression, "expression")
3977            then = f"{this} VALUES {then}" if then else this
3978        elif isinstance(then_expression, exp.Update):
3979            if isinstance(then_expression.args.get("expressions"), exp.Star):
3980                then = f"UPDATE {self.sql(then_expression, 'expressions')}"
3981            else:
3982                then = f"UPDATE SET{self.sep()}{self.expressions(then_expression)}"
3983        else:
3984            then = self.sql(then_expression)
3985        return f"WHEN {matched}{source}{condition} THEN {then}"
3986
3987    def whens_sql(self, expression: exp.Whens) -> str:
3988        return self.expressions(expression, sep=" ", indent=False)
3989
3990    def merge_sql(self, expression: exp.Merge) -> str:
3991        table = expression.this
3992        table_alias = ""
3993
3994        hints = table.args.get("hints")
3995        if hints and table.alias and isinstance(hints[0], exp.WithTableHint):
3996            # T-SQL syntax is MERGE ... <target_table> [WITH (<merge_hint>)] [[AS] table_alias]
3997            table_alias = f" AS {self.sql(table.args['alias'].pop())}"
3998
3999        this = self.sql(table)
4000        using = f"USING {self.sql(expression, 'using')}"
4001        on = f"ON {self.sql(expression, 'on')}"
4002        whens = self.sql(expression, "whens")
4003
4004        returning = self.sql(expression, "returning")
4005        if returning:
4006            whens = f"{whens}{returning}"
4007
4008        sep = self.sep()
4009
4010        return self.prepend_ctes(
4011            expression,
4012            f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{whens}",
4013        )
4014
4015    @unsupported_args("format")
4016    def tochar_sql(self, expression: exp.ToChar) -> str:
4017        return self.sql(exp.cast(expression.this, exp.DataType.Type.TEXT))
4018
4019    def tonumber_sql(self, expression: exp.ToNumber) -> str:
4020        if not self.SUPPORTS_TO_NUMBER:
4021            self.unsupported("Unsupported TO_NUMBER function")
4022            return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
4023
4024        fmt = expression.args.get("format")
4025        if not fmt:
4026            self.unsupported("Conversion format is required for TO_NUMBER")
4027            return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
4028
4029        return self.func("TO_NUMBER", expression.this, fmt)
4030
4031    def dictproperty_sql(self, expression: exp.DictProperty) -> str:
4032        this = self.sql(expression, "this")
4033        kind = self.sql(expression, "kind")
4034        settings_sql = self.expressions(expression, key="settings", sep=" ")
4035        args = f"({self.sep('')}{settings_sql}{self.seg(')', sep='')}" if settings_sql else "()"
4036        return f"{this}({kind}{args})"
4037
4038    def dictrange_sql(self, expression: exp.DictRange) -> str:
4039        this = self.sql(expression, "this")
4040        max = self.sql(expression, "max")
4041        min = self.sql(expression, "min")
4042        return f"{this}(MIN {min} MAX {max})"
4043
4044    def dictsubproperty_sql(self, expression: exp.DictSubProperty) -> str:
4045        return f"{self.sql(expression, 'this')} {self.sql(expression, 'value')}"
4046
4047    def duplicatekeyproperty_sql(self, expression: exp.DuplicateKeyProperty) -> str:
4048        return f"DUPLICATE KEY ({self.expressions(expression, flat=True)})"
4049
4050    # https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/
4051    def uniquekeyproperty_sql(
4052        self, expression: exp.UniqueKeyProperty, prefix: str = "UNIQUE KEY"
4053    ) -> str:
4054        return f"{prefix} ({self.expressions(expression, flat=True)})"
4055
4056    # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
4057    def distributedbyproperty_sql(self, expression: exp.DistributedByProperty) -> str:
4058        expressions = self.expressions(expression, flat=True)
4059        expressions = f" {self.wrap(expressions)}" if expressions else ""
4060        buckets = self.sql(expression, "buckets")
4061        kind = self.sql(expression, "kind")
4062        buckets = f" BUCKETS {buckets}" if buckets else ""
4063        order = self.sql(expression, "order")
4064        return f"DISTRIBUTED BY {kind}{expressions}{buckets}{order}"
4065
4066    def oncluster_sql(self, expression: exp.OnCluster) -> str:
4067        return ""
4068
4069    def clusteredbyproperty_sql(self, expression: exp.ClusteredByProperty) -> str:
4070        expressions = self.expressions(expression, key="expressions", flat=True)
4071        sorted_by = self.expressions(expression, key="sorted_by", flat=True)
4072        sorted_by = f" SORTED BY ({sorted_by})" if sorted_by else ""
4073        buckets = self.sql(expression, "buckets")
4074        return f"CLUSTERED BY ({expressions}){sorted_by} INTO {buckets} BUCKETS"
4075
4076    def anyvalue_sql(self, expression: exp.AnyValue) -> str:
4077        this = self.sql(expression, "this")
4078        having = self.sql(expression, "having")
4079
4080        if having:
4081            this = f"{this} HAVING {'MAX' if expression.args.get('max') else 'MIN'} {having}"
4082
4083        return self.func("ANY_VALUE", this)
4084
4085    def querytransform_sql(self, expression: exp.QueryTransform) -> str:
4086        transform = self.func("TRANSFORM", *expression.expressions)
4087        row_format_before = self.sql(expression, "row_format_before")
4088        row_format_before = f" {row_format_before}" if row_format_before else ""
4089        record_writer = self.sql(expression, "record_writer")
4090        record_writer = f" RECORDWRITER {record_writer}" if record_writer else ""
4091        using = f" USING {self.sql(expression, 'command_script')}"
4092        schema = self.sql(expression, "schema")
4093        schema = f" AS {schema}" if schema else ""
4094        row_format_after = self.sql(expression, "row_format_after")
4095        row_format_after = f" {row_format_after}" if row_format_after else ""
4096        record_reader = self.sql(expression, "record_reader")
4097        record_reader = f" RECORDREADER {record_reader}" if record_reader else ""
4098        return f"{transform}{row_format_before}{record_writer}{using}{schema}{row_format_after}{record_reader}"
4099
4100    def indexconstraintoption_sql(self, expression: exp.IndexConstraintOption) -> str:
4101        key_block_size = self.sql(expression, "key_block_size")
4102        if key_block_size:
4103            return f"KEY_BLOCK_SIZE = {key_block_size}"
4104
4105        using = self.sql(expression, "using")
4106        if using:
4107            return f"USING {using}"
4108
4109        parser = self.sql(expression, "parser")
4110        if parser:
4111            return f"WITH PARSER {parser}"
4112
4113        comment = self.sql(expression, "comment")
4114        if comment:
4115            return f"COMMENT {comment}"
4116
4117        visible = expression.args.get("visible")
4118        if visible is not None:
4119            return "VISIBLE" if visible else "INVISIBLE"
4120
4121        engine_attr = self.sql(expression, "engine_attr")
4122        if engine_attr:
4123            return f"ENGINE_ATTRIBUTE = {engine_attr}"
4124
4125        secondary_engine_attr = self.sql(expression, "secondary_engine_attr")
4126        if secondary_engine_attr:
4127            return f"SECONDARY_ENGINE_ATTRIBUTE = {secondary_engine_attr}"
4128
4129        self.unsupported("Unsupported index constraint option.")
4130        return ""
4131
4132    def checkcolumnconstraint_sql(self, expression: exp.CheckColumnConstraint) -> str:
4133        enforced = " ENFORCED" if expression.args.get("enforced") else ""
4134        return f"CHECK ({self.sql(expression, 'this')}){enforced}"
4135
4136    def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
4137        kind = self.sql(expression, "kind")
4138        kind = f"{kind} INDEX" if kind else "INDEX"
4139        this = self.sql(expression, "this")
4140        this = f" {this}" if this else ""
4141        index_type = self.sql(expression, "index_type")
4142        index_type = f" USING {index_type}" if index_type else ""
4143        expressions = self.expressions(expression, flat=True)
4144        expressions = f" ({expressions})" if expressions else ""
4145        options = self.expressions(expression, key="options", sep=" ")
4146        options = f" {options}" if options else ""
4147        return f"{kind}{this}{index_type}{expressions}{options}"
4148
4149    def nvl2_sql(self, expression: exp.Nvl2) -> str:
4150        if self.NVL2_SUPPORTED:
4151            return self.function_fallback_sql(expression)
4152
4153        case = exp.Case().when(
4154            expression.this.is_(exp.null()).not_(copy=False),
4155            expression.args["true"],
4156            copy=False,
4157        )
4158        else_cond = expression.args.get("false")
4159        if else_cond:
4160            case.else_(else_cond, copy=False)
4161
4162        return self.sql(case)
4163
4164    def comprehension_sql(self, expression: exp.Comprehension) -> str:
4165        this = self.sql(expression, "this")
4166        expr = self.sql(expression, "expression")
4167        iterator = self.sql(expression, "iterator")
4168        condition = self.sql(expression, "condition")
4169        condition = f" IF {condition}" if condition else ""
4170        return f"{this} FOR {expr} IN {iterator}{condition}"
4171
4172    def columnprefix_sql(self, expression: exp.ColumnPrefix) -> str:
4173        return f"{self.sql(expression, 'this')}({self.sql(expression, 'expression')})"
4174
4175    def opclass_sql(self, expression: exp.Opclass) -> str:
4176        return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
4177
4178    def predict_sql(self, expression: exp.Predict) -> str:
4179        model = self.sql(expression, "this")
4180        model = f"MODEL {model}"
4181        table = self.sql(expression, "expression")
4182        table = f"TABLE {table}" if not isinstance(expression.expression, exp.Subquery) else table
4183        parameters = self.sql(expression, "params_struct")
4184        return self.func("PREDICT", model, table, parameters or None)
4185
4186    def generateembedding_sql(self, expression: exp.GenerateEmbedding) -> str:
4187        model = self.sql(expression, "this")
4188        model = f"MODEL {model}"
4189        table = self.sql(expression, "expression")
4190        table = f"TABLE {table}" if not isinstance(expression.expression, exp.Subquery) else table
4191        parameters = self.sql(expression, "params_struct")
4192        return self.func("GENERATE_EMBEDDING", model, table, parameters or None)
4193
4194    def featuresattime_sql(self, expression: exp.FeaturesAtTime) -> str:
4195        this_sql = self.sql(expression, "this")
4196        if isinstance(expression.this, exp.Table):
4197            this_sql = f"TABLE {this_sql}"
4198
4199        return self.func(
4200            "FEATURES_AT_TIME",
4201            this_sql,
4202            expression.args.get("time"),
4203            expression.args.get("num_rows"),
4204            expression.args.get("ignore_feature_nulls"),
4205        )
4206
4207    def vectorsearch_sql(self, expression: exp.VectorSearch) -> str:
4208        this_sql = self.sql(expression, "this")
4209        if isinstance(expression.this, exp.Table):
4210            this_sql = f"TABLE {this_sql}"
4211
4212        query_table = self.sql(expression, "query_table")
4213        if isinstance(expression.args["query_table"], exp.Table):
4214            query_table = f"TABLE {query_table}"
4215
4216        return self.func(
4217            "VECTOR_SEARCH",
4218            this_sql,
4219            expression.args.get("column_to_search"),
4220            query_table,
4221            expression.args.get("query_column_to_search"),
4222            expression.args.get("top_k"),
4223            expression.args.get("distance_type"),
4224            expression.args.get("options"),
4225        )
4226
4227    def forin_sql(self, expression: exp.ForIn) -> str:
4228        this = self.sql(expression, "this")
4229        expression_sql = self.sql(expression, "expression")
4230        return f"FOR {this} DO {expression_sql}"
4231
4232    def refresh_sql(self, expression: exp.Refresh) -> str:
4233        this = self.sql(expression, "this")
4234        table = "" if isinstance(expression.this, exp.Literal) else "TABLE "
4235        return f"REFRESH {table}{this}"
4236
4237    def toarray_sql(self, expression: exp.ToArray) -> str:
4238        arg = expression.this
4239        if not arg.type:
4240            from sqlglot.optimizer.annotate_types import annotate_types
4241
4242            arg = annotate_types(arg, dialect=self.dialect)
4243
4244        if arg.is_type(exp.DataType.Type.ARRAY):
4245            return self.sql(arg)
4246
4247        cond_for_null = arg.is_(exp.null())
4248        return self.sql(exp.func("IF", cond_for_null, exp.null(), exp.array(arg, copy=False)))
4249
4250    def tsordstotime_sql(self, expression: exp.TsOrDsToTime) -> str:
4251        this = expression.this
4252        time_format = self.format_time(expression)
4253
4254        if time_format:
4255            return self.sql(
4256                exp.cast(
4257                    exp.StrToTime(this=this, format=expression.args["format"]),
4258                    exp.DataType.Type.TIME,
4259                )
4260            )
4261
4262        if isinstance(this, exp.TsOrDsToTime) or this.is_type(exp.DataType.Type.TIME):
4263            return self.sql(this)
4264
4265        return self.sql(exp.cast(this, exp.DataType.Type.TIME))
4266
4267    def tsordstotimestamp_sql(self, expression: exp.TsOrDsToTimestamp) -> str:
4268        this = expression.this
4269        if isinstance(this, exp.TsOrDsToTimestamp) or this.is_type(exp.DataType.Type.TIMESTAMP):
4270            return self.sql(this)
4271
4272        return self.sql(exp.cast(this, exp.DataType.Type.TIMESTAMP, dialect=self.dialect))
4273
4274    def tsordstodatetime_sql(self, expression: exp.TsOrDsToDatetime) -> str:
4275        this = expression.this
4276        if isinstance(this, exp.TsOrDsToDatetime) or this.is_type(exp.DataType.Type.DATETIME):
4277            return self.sql(this)
4278
4279        return self.sql(exp.cast(this, exp.DataType.Type.DATETIME, dialect=self.dialect))
4280
4281    def tsordstodate_sql(self, expression: exp.TsOrDsToDate) -> str:
4282        this = expression.this
4283        time_format = self.format_time(expression)
4284
4285        if time_format and time_format not in (self.dialect.TIME_FORMAT, self.dialect.DATE_FORMAT):
4286            return self.sql(
4287                exp.cast(
4288                    exp.StrToTime(this=this, format=expression.args["format"]),
4289                    exp.DataType.Type.DATE,
4290                )
4291            )
4292
4293        if isinstance(this, exp.TsOrDsToDate) or this.is_type(exp.DataType.Type.DATE):
4294            return self.sql(this)
4295
4296        return self.sql(exp.cast(this, exp.DataType.Type.DATE))
4297
4298    def unixdate_sql(self, expression: exp.UnixDate) -> str:
4299        return self.sql(
4300            exp.func(
4301                "DATEDIFF",
4302                expression.this,
4303                exp.cast(exp.Literal.string("1970-01-01"), exp.DataType.Type.DATE),
4304                "day",
4305            )
4306        )
4307
4308    def lastday_sql(self, expression: exp.LastDay) -> str:
4309        if self.LAST_DAY_SUPPORTS_DATE_PART:
4310            return self.function_fallback_sql(expression)
4311
4312        unit = expression.text("unit")
4313        if unit and unit != "MONTH":
4314            self.unsupported("Date parts are not supported in LAST_DAY.")
4315
4316        return self.func("LAST_DAY", expression.this)
4317
4318    def dateadd_sql(self, expression: exp.DateAdd) -> str:
4319        from sqlglot.dialects.dialect import unit_to_str
4320
4321        return self.func(
4322            "DATE_ADD", expression.this, expression.expression, unit_to_str(expression)
4323        )
4324
4325    def arrayany_sql(self, expression: exp.ArrayAny) -> str:
4326        if self.CAN_IMPLEMENT_ARRAY_ANY:
4327            filtered = exp.ArrayFilter(this=expression.this, expression=expression.expression)
4328            filtered_not_empty = exp.ArraySize(this=filtered).neq(0)
4329            original_is_empty = exp.ArraySize(this=expression.this).eq(0)
4330            return self.sql(exp.paren(original_is_empty.or_(filtered_not_empty)))
4331
4332        from sqlglot.dialects import Dialect
4333
4334        # SQLGlot's executor supports ARRAY_ANY, so we don't wanna warn for the SQLGlot dialect
4335        if self.dialect.__class__ != Dialect:
4336            self.unsupported("ARRAY_ANY is unsupported")
4337
4338        return self.function_fallback_sql(expression)
4339
4340    def struct_sql(self, expression: exp.Struct) -> str:
4341        expression.set(
4342            "expressions",
4343            [
4344                exp.alias_(e.expression, e.name if e.this.is_string else e.this)
4345                if isinstance(e, exp.PropertyEQ)
4346                else e
4347                for e in expression.expressions
4348            ],
4349        )
4350
4351        return self.function_fallback_sql(expression)
4352
4353    def partitionrange_sql(self, expression: exp.PartitionRange) -> str:
4354        low = self.sql(expression, "this")
4355        high = self.sql(expression, "expression")
4356
4357        return f"{low} TO {high}"
4358
4359    def truncatetable_sql(self, expression: exp.TruncateTable) -> str:
4360        target = "DATABASE" if expression.args.get("is_database") else "TABLE"
4361        tables = f" {self.expressions(expression)}"
4362
4363        exists = " IF EXISTS" if expression.args.get("exists") else ""
4364
4365        on_cluster = self.sql(expression, "cluster")
4366        on_cluster = f" {on_cluster}" if on_cluster else ""
4367
4368        identity = self.sql(expression, "identity")
4369        identity = f" {identity} IDENTITY" if identity else ""
4370
4371        option = self.sql(expression, "option")
4372        option = f" {option}" if option else ""
4373
4374        partition = self.sql(expression, "partition")
4375        partition = f" {partition}" if partition else ""
4376
4377        return f"TRUNCATE {target}{exists}{tables}{on_cluster}{identity}{option}{partition}"
4378
4379    # This transpiles T-SQL's CONVERT function
4380    # https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16
4381    def convert_sql(self, expression: exp.Convert) -> str:
4382        to = expression.this
4383        value = expression.expression
4384        style = expression.args.get("style")
4385        safe = expression.args.get("safe")
4386        strict = expression.args.get("strict")
4387
4388        if not to or not value:
4389            return ""
4390
4391        # Retrieve length of datatype and override to default if not specified
4392        if not seq_get(to.expressions, 0) and to.this in self.PARAMETERIZABLE_TEXT_TYPES:
4393            to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False)
4394
4395        transformed: t.Optional[exp.Expression] = None
4396        cast = exp.Cast if strict else exp.TryCast
4397
4398        # Check whether a conversion with format (T-SQL calls this 'style') is applicable
4399        if isinstance(style, exp.Literal) and style.is_int:
4400            from sqlglot.dialects.tsql import TSQL
4401
4402            style_value = style.name
4403            converted_style = TSQL.CONVERT_FORMAT_MAPPING.get(style_value)
4404            if not converted_style:
4405                self.unsupported(f"Unsupported T-SQL 'style' value: {style_value}")
4406
4407            fmt = exp.Literal.string(converted_style)
4408
4409            if to.this == exp.DataType.Type.DATE:
4410                transformed = exp.StrToDate(this=value, format=fmt)
4411            elif to.this in (exp.DataType.Type.DATETIME, exp.DataType.Type.DATETIME2):
4412                transformed = exp.StrToTime(this=value, format=fmt)
4413            elif to.this in self.PARAMETERIZABLE_TEXT_TYPES:
4414                transformed = cast(this=exp.TimeToStr(this=value, format=fmt), to=to, safe=safe)
4415            elif to.this == exp.DataType.Type.TEXT:
4416                transformed = exp.TimeToStr(this=value, format=fmt)
4417
4418        if not transformed:
4419            transformed = cast(this=value, to=to, safe=safe)
4420
4421        return self.sql(transformed)
4422
4423    def _jsonpathkey_sql(self, expression: exp.JSONPathKey) -> str:
4424        this = expression.this
4425        if isinstance(this, exp.JSONPathWildcard):
4426            this = self.json_path_part(this)
4427            return f".{this}" if this else ""
4428
4429        if self.SAFE_JSON_PATH_KEY_RE.match(this):
4430            return f".{this}"
4431
4432        this = self.json_path_part(this)
4433        return (
4434            f"[{this}]"
4435            if self._quote_json_path_key_using_brackets and self.JSON_PATH_BRACKETED_KEY_SUPPORTED
4436            else f".{this}"
4437        )
4438
4439    def _jsonpathsubscript_sql(self, expression: exp.JSONPathSubscript) -> str:
4440        this = self.json_path_part(expression.this)
4441        return f"[{this}]" if this else ""
4442
4443    def _simplify_unless_literal(self, expression: E) -> E:
4444        if not isinstance(expression, exp.Literal):
4445            from sqlglot.optimizer.simplify import simplify
4446
4447            expression = simplify(expression, dialect=self.dialect)
4448
4449        return expression
4450
4451    def _embed_ignore_nulls(self, expression: exp.IgnoreNulls | exp.RespectNulls, text: str) -> str:
4452        this = expression.this
4453        if isinstance(this, self.RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS):
4454            self.unsupported(
4455                f"RESPECT/IGNORE NULLS is not supported for {type(this).key} in {self.dialect.__class__.__name__}"
4456            )
4457            return self.sql(this)
4458
4459        if self.IGNORE_NULLS_IN_FUNC and not expression.meta.get("inline"):
4460            # The first modifier here will be the one closest to the AggFunc's arg
4461            mods = sorted(
4462                expression.find_all(exp.HavingMax, exp.Order, exp.Limit),
4463                key=lambda x: 0
4464                if isinstance(x, exp.HavingMax)
4465                else (1 if isinstance(x, exp.Order) else 2),
4466            )
4467
4468            if mods:
4469                mod = mods[0]
4470                this = expression.__class__(this=mod.this.copy())
4471                this.meta["inline"] = True
4472                mod.this.replace(this)
4473                return self.sql(expression.this)
4474
4475            agg_func = expression.find(exp.AggFunc)
4476
4477            if agg_func:
4478                agg_func_sql = self.sql(agg_func, comment=False)[:-1] + f" {text})"
4479                return self.maybe_comment(agg_func_sql, comments=agg_func.comments)
4480
4481        return f"{self.sql(expression, 'this')} {text}"
4482
4483    def _replace_line_breaks(self, string: str) -> str:
4484        """We don't want to extra indent line breaks so we temporarily replace them with sentinels."""
4485        if self.pretty:
4486            return string.replace("\n", self.SENTINEL_LINE_BREAK)
4487        return string
4488
4489    def copyparameter_sql(self, expression: exp.CopyParameter) -> str:
4490        option = self.sql(expression, "this")
4491
4492        if expression.expressions:
4493            upper = option.upper()
4494
4495            # Snowflake FILE_FORMAT options are separated by whitespace
4496            sep = " " if upper == "FILE_FORMAT" else ", "
4497
4498            # Databricks copy/format options do not set their list of values with EQ
4499            op = " " if upper in ("COPY_OPTIONS", "FORMAT_OPTIONS") else " = "
4500            values = self.expressions(expression, flat=True, sep=sep)
4501            return f"{option}{op}({values})"
4502
4503        value = self.sql(expression, "expression")
4504
4505        if not value:
4506            return option
4507
4508        op = " = " if self.COPY_PARAMS_EQ_REQUIRED else " "
4509
4510        return f"{option}{op}{value}"
4511
4512    def credentials_sql(self, expression: exp.Credentials) -> str:
4513        cred_expr = expression.args.get("credentials")
4514        if isinstance(cred_expr, exp.Literal):
4515            # Redshift case: CREDENTIALS <string>
4516            credentials = self.sql(expression, "credentials")
4517            credentials = f"CREDENTIALS {credentials}" if credentials else ""
4518        else:
4519            # Snowflake case: CREDENTIALS = (...)
4520            credentials = self.expressions(expression, key="credentials", flat=True, sep=" ")
4521            credentials = f"CREDENTIALS = ({credentials})" if cred_expr is not None else ""
4522
4523        storage = self.sql(expression, "storage")
4524        storage = f"STORAGE_INTEGRATION = {storage}" if storage else ""
4525
4526        encryption = self.expressions(expression, key="encryption", flat=True, sep=" ")
4527        encryption = f" ENCRYPTION = ({encryption})" if encryption else ""
4528
4529        iam_role = self.sql(expression, "iam_role")
4530        iam_role = f"IAM_ROLE {iam_role}" if iam_role else ""
4531
4532        region = self.sql(expression, "region")
4533        region = f" REGION {region}" if region else ""
4534
4535        return f"{credentials}{storage}{encryption}{iam_role}{region}"
4536
4537    def copy_sql(self, expression: exp.Copy) -> str:
4538        this = self.sql(expression, "this")
4539        this = f" INTO {this}" if self.COPY_HAS_INTO_KEYWORD else f" {this}"
4540
4541        credentials = self.sql(expression, "credentials")
4542        credentials = self.seg(credentials) if credentials else ""
4543        kind = self.seg("FROM" if expression.args.get("kind") else "TO")
4544        files = self.expressions(expression, key="files", flat=True)
4545
4546        sep = ", " if self.dialect.COPY_PARAMS_ARE_CSV else " "
4547        params = self.expressions(
4548            expression,
4549            key="params",
4550            sep=sep,
4551            new_line=True,
4552            skip_last=True,
4553            skip_first=True,
4554            indent=self.COPY_PARAMS_ARE_WRAPPED,
4555        )
4556
4557        if params:
4558            if self.COPY_PARAMS_ARE_WRAPPED:
4559                params = f" WITH ({params})"
4560            elif not self.pretty:
4561                params = f" {params}"
4562
4563        return f"COPY{this}{kind} {files}{credentials}{params}"
4564
4565    def semicolon_sql(self, expression: exp.Semicolon) -> str:
4566        return ""
4567
4568    def datadeletionproperty_sql(self, expression: exp.DataDeletionProperty) -> str:
4569        on_sql = "ON" if expression.args.get("on") else "OFF"
4570        filter_col: t.Optional[str] = self.sql(expression, "filter_column")
4571        filter_col = f"FILTER_COLUMN={filter_col}" if filter_col else None
4572        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
4573        retention_period = f"RETENTION_PERIOD={retention_period}" if retention_period else None
4574
4575        if filter_col or retention_period:
4576            on_sql = self.func("ON", filter_col, retention_period)
4577
4578        return f"DATA_DELETION={on_sql}"
4579
4580    def maskingpolicycolumnconstraint_sql(
4581        self, expression: exp.MaskingPolicyColumnConstraint
4582    ) -> str:
4583        this = self.sql(expression, "this")
4584        expressions = self.expressions(expression, flat=True)
4585        expressions = f" USING ({expressions})" if expressions else ""
4586        return f"MASKING POLICY {this}{expressions}"
4587
4588    def gapfill_sql(self, expression: exp.GapFill) -> str:
4589        this = self.sql(expression, "this")
4590        this = f"TABLE {this}"
4591        return self.func("GAP_FILL", this, *[v for k, v in expression.args.items() if k != "this"])
4592
4593    def scope_resolution(self, rhs: str, scope_name: str) -> str:
4594        return self.func("SCOPE_RESOLUTION", scope_name or None, rhs)
4595
4596    def scoperesolution_sql(self, expression: exp.ScopeResolution) -> str:
4597        this = self.sql(expression, "this")
4598        expr = expression.expression
4599
4600        if isinstance(expr, exp.Func):
4601            # T-SQL's CLR functions are case sensitive
4602            expr = f"{self.sql(expr, 'this')}({self.format_args(*expr.expressions)})"
4603        else:
4604            expr = self.sql(expression, "expression")
4605
4606        return self.scope_resolution(expr, this)
4607
4608    def parsejson_sql(self, expression: exp.ParseJSON) -> str:
4609        if self.PARSE_JSON_NAME is None:
4610            return self.sql(expression.this)
4611
4612        return self.func(self.PARSE_JSON_NAME, expression.this, expression.expression)
4613
4614    def rand_sql(self, expression: exp.Rand) -> str:
4615        lower = self.sql(expression, "lower")
4616        upper = self.sql(expression, "upper")
4617
4618        if lower and upper:
4619            return f"({upper} - {lower}) * {self.func('RAND', expression.this)} + {lower}"
4620        return self.func("RAND", expression.this)
4621
4622    def changes_sql(self, expression: exp.Changes) -> str:
4623        information = self.sql(expression, "information")
4624        information = f"INFORMATION => {information}"
4625        at_before = self.sql(expression, "at_before")
4626        at_before = f"{self.seg('')}{at_before}" if at_before else ""
4627        end = self.sql(expression, "end")
4628        end = f"{self.seg('')}{end}" if end else ""
4629
4630        return f"CHANGES ({information}){at_before}{end}"
4631
4632    def pad_sql(self, expression: exp.Pad) -> str:
4633        prefix = "L" if expression.args.get("is_left") else "R"
4634
4635        fill_pattern = self.sql(expression, "fill_pattern") or None
4636        if not fill_pattern and self.PAD_FILL_PATTERN_IS_REQUIRED:
4637            fill_pattern = "' '"
4638
4639        return self.func(f"{prefix}PAD", expression.this, expression.expression, fill_pattern)
4640
4641    def summarize_sql(self, expression: exp.Summarize) -> str:
4642        table = " TABLE" if expression.args.get("table") else ""
4643        return f"SUMMARIZE{table} {self.sql(expression.this)}"
4644
4645    def explodinggenerateseries_sql(self, expression: exp.ExplodingGenerateSeries) -> str:
4646        generate_series = exp.GenerateSeries(**expression.args)
4647
4648        parent = expression.parent
4649        if isinstance(parent, (exp.Alias, exp.TableAlias)):
4650            parent = parent.parent
4651
4652        if self.SUPPORTS_EXPLODING_PROJECTIONS and not isinstance(parent, (exp.Table, exp.Unnest)):
4653            return self.sql(exp.Unnest(expressions=[generate_series]))
4654
4655        if isinstance(parent, exp.Select):
4656            self.unsupported("GenerateSeries projection unnesting is not supported.")
4657
4658        return self.sql(generate_series)
4659
4660    def arrayconcat_sql(self, expression: exp.ArrayConcat, name: str = "ARRAY_CONCAT") -> str:
4661        exprs = expression.expressions
4662        if not self.ARRAY_CONCAT_IS_VAR_LEN:
4663            rhs = reduce(lambda x, y: exp.ArrayConcat(this=x, expressions=[y]), exprs)
4664        else:
4665            rhs = self.expressions(expression)
4666
4667        return self.func(name, expression.this, rhs or None)
4668
4669    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
4670        if self.SUPPORTS_CONVERT_TIMEZONE:
4671            return self.function_fallback_sql(expression)
4672
4673        source_tz = expression.args.get("source_tz")
4674        target_tz = expression.args.get("target_tz")
4675        timestamp = expression.args.get("timestamp")
4676
4677        if source_tz and timestamp:
4678            timestamp = exp.AtTimeZone(
4679                this=exp.cast(timestamp, exp.DataType.Type.TIMESTAMPNTZ), zone=source_tz
4680            )
4681
4682        expr = exp.AtTimeZone(this=timestamp, zone=target_tz)
4683
4684        return self.sql(expr)
4685
4686    def json_sql(self, expression: exp.JSON) -> str:
4687        this = self.sql(expression, "this")
4688        this = f" {this}" if this else ""
4689
4690        _with = expression.args.get("with")
4691
4692        if _with is None:
4693            with_sql = ""
4694        elif not _with:
4695            with_sql = " WITHOUT"
4696        else:
4697            with_sql = " WITH"
4698
4699        unique_sql = " UNIQUE KEYS" if expression.args.get("unique") else ""
4700
4701        return f"JSON{this}{with_sql}{unique_sql}"
4702
4703    def jsonvalue_sql(self, expression: exp.JSONValue) -> str:
4704        def _generate_on_options(arg: t.Any) -> str:
4705            return arg if isinstance(arg, str) else f"DEFAULT {self.sql(arg)}"
4706
4707        path = self.sql(expression, "path")
4708        returning = self.sql(expression, "returning")
4709        returning = f" RETURNING {returning}" if returning else ""
4710
4711        on_condition = self.sql(expression, "on_condition")
4712        on_condition = f" {on_condition}" if on_condition else ""
4713
4714        return self.func("JSON_VALUE", expression.this, f"{path}{returning}{on_condition}")
4715
4716    def conditionalinsert_sql(self, expression: exp.ConditionalInsert) -> str:
4717        else_ = "ELSE " if expression.args.get("else_") else ""
4718        condition = self.sql(expression, "expression")
4719        condition = f"WHEN {condition} THEN " if condition else else_
4720        insert = self.sql(expression, "this")[len("INSERT") :].strip()
4721        return f"{condition}{insert}"
4722
4723    def multitableinserts_sql(self, expression: exp.MultitableInserts) -> str:
4724        kind = self.sql(expression, "kind")
4725        expressions = self.seg(self.expressions(expression, sep=" "))
4726        res = f"INSERT {kind}{expressions}{self.seg(self.sql(expression, 'source'))}"
4727        return res
4728
4729    def oncondition_sql(self, expression: exp.OnCondition) -> str:
4730        # Static options like "NULL ON ERROR" are stored as strings, in contrast to "DEFAULT <expr> ON ERROR"
4731        empty = expression.args.get("empty")
4732        empty = (
4733            f"DEFAULT {empty} ON EMPTY"
4734            if isinstance(empty, exp.Expression)
4735            else self.sql(expression, "empty")
4736        )
4737
4738        error = expression.args.get("error")
4739        error = (
4740            f"DEFAULT {error} ON ERROR"
4741            if isinstance(error, exp.Expression)
4742            else self.sql(expression, "error")
4743        )
4744
4745        if error and empty:
4746            error = (
4747                f"{empty} {error}"
4748                if self.dialect.ON_CONDITION_EMPTY_BEFORE_ERROR
4749                else f"{error} {empty}"
4750            )
4751            empty = ""
4752
4753        null = self.sql(expression, "null")
4754
4755        return f"{empty}{error}{null}"
4756
4757    def jsonextractquote_sql(self, expression: exp.JSONExtractQuote) -> str:
4758        scalar = " ON SCALAR STRING" if expression.args.get("scalar") else ""
4759        return f"{self.sql(expression, 'option')} QUOTES{scalar}"
4760
4761    def jsonexists_sql(self, expression: exp.JSONExists) -> str:
4762        this = self.sql(expression, "this")
4763        path = self.sql(expression, "path")
4764
4765        passing = self.expressions(expression, "passing")
4766        passing = f" PASSING {passing}" if passing else ""
4767
4768        on_condition = self.sql(expression, "on_condition")
4769        on_condition = f" {on_condition}" if on_condition else ""
4770
4771        path = f"{path}{passing}{on_condition}"
4772
4773        return self.func("JSON_EXISTS", this, path)
4774
4775    def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
4776        array_agg = self.function_fallback_sql(expression)
4777
4778        # Add a NULL FILTER on the column to mimic the results going from a dialect that excludes nulls
4779        # on ARRAY_AGG (e.g Spark) to one that doesn't (e.g. DuckDB)
4780        if self.dialect.ARRAY_AGG_INCLUDES_NULLS and expression.args.get("nulls_excluded"):
4781            parent = expression.parent
4782            if isinstance(parent, exp.Filter):
4783                parent_cond = parent.expression.this
4784                parent_cond.replace(parent_cond.and_(expression.this.is_(exp.null()).not_()))
4785            else:
4786                this = expression.this
4787                # Do not add the filter if the input is not a column (e.g. literal, struct etc)
4788                if this.find(exp.Column):
4789                    # DISTINCT is already present in the agg function, do not propagate it to FILTER as well
4790                    this_sql = (
4791                        self.expressions(this)
4792                        if isinstance(this, exp.Distinct)
4793                        else self.sql(expression, "this")
4794                    )
4795
4796                    array_agg = f"{array_agg} FILTER(WHERE {this_sql} IS NOT NULL)"
4797
4798        return array_agg
4799
4800    def apply_sql(self, expression: exp.Apply) -> str:
4801        this = self.sql(expression, "this")
4802        expr = self.sql(expression, "expression")
4803
4804        return f"{this} APPLY({expr})"
4805
4806    def grant_sql(self, expression: exp.Grant) -> str:
4807        privileges_sql = self.expressions(expression, key="privileges", flat=True)
4808
4809        kind = self.sql(expression, "kind")
4810        kind = f" {kind}" if kind else ""
4811
4812        securable = self.sql(expression, "securable")
4813        securable = f" {securable}" if securable else ""
4814
4815        principals = self.expressions(expression, key="principals", flat=True)
4816
4817        grant_option = " WITH GRANT OPTION" if expression.args.get("grant_option") else ""
4818
4819        return f"GRANT {privileges_sql} ON{kind}{securable} TO {principals}{grant_option}"
4820
4821    def grantprivilege_sql(self, expression: exp.GrantPrivilege):
4822        this = self.sql(expression, "this")
4823        columns = self.expressions(expression, flat=True)
4824        columns = f"({columns})" if columns else ""
4825
4826        return f"{this}{columns}"
4827
4828    def grantprincipal_sql(self, expression: exp.GrantPrincipal):
4829        this = self.sql(expression, "this")
4830
4831        kind = self.sql(expression, "kind")
4832        kind = f"{kind} " if kind else ""
4833
4834        return f"{kind}{this}"
4835
4836    def columns_sql(self, expression: exp.Columns):
4837        func = self.function_fallback_sql(expression)
4838        if expression.args.get("unpack"):
4839            func = f"*{func}"
4840
4841        return func
4842
4843    def overlay_sql(self, expression: exp.Overlay):
4844        this = self.sql(expression, "this")
4845        expr = self.sql(expression, "expression")
4846        from_sql = self.sql(expression, "from")
4847        for_sql = self.sql(expression, "for")
4848        for_sql = f" FOR {for_sql}" if for_sql else ""
4849
4850        return f"OVERLAY({this} PLACING {expr} FROM {from_sql}{for_sql})"
4851
4852    @unsupported_args("format")
4853    def todouble_sql(self, expression: exp.ToDouble) -> str:
4854        return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
4855
4856    def string_sql(self, expression: exp.String) -> str:
4857        this = expression.this
4858        zone = expression.args.get("zone")
4859
4860        if zone:
4861            # This is a BigQuery specific argument for STRING(<timestamp_expr>, <time_zone>)
4862            # BigQuery stores timestamps internally as UTC, so ConvertTimezone is used with UTC
4863            # set for source_tz to transpile the time conversion before the STRING cast
4864            this = exp.ConvertTimezone(
4865                source_tz=exp.Literal.string("UTC"), target_tz=zone, timestamp=this
4866            )
4867
4868        return self.sql(exp.cast(this, exp.DataType.Type.VARCHAR))
4869
4870    def median_sql(self, expression: exp.Median):
4871        if not self.SUPPORTS_MEDIAN:
4872            return self.sql(
4873                exp.PercentileCont(this=expression.this, expression=exp.Literal.number(0.5))
4874            )
4875
4876        return self.function_fallback_sql(expression)
4877
4878    def overflowtruncatebehavior_sql(self, expression: exp.OverflowTruncateBehavior) -> str:
4879        filler = self.sql(expression, "this")
4880        filler = f" {filler}" if filler else ""
4881        with_count = "WITH COUNT" if expression.args.get("with_count") else "WITHOUT COUNT"
4882        return f"TRUNCATE{filler} {with_count}"
4883
4884    def unixseconds_sql(self, expression: exp.UnixSeconds) -> str:
4885        if self.SUPPORTS_UNIX_SECONDS:
4886            return self.function_fallback_sql(expression)
4887
4888        start_ts = exp.cast(
4889            exp.Literal.string("1970-01-01 00:00:00+00"), to=exp.DataType.Type.TIMESTAMPTZ
4890        )
4891
4892        return self.sql(
4893            exp.TimestampDiff(this=expression.this, expression=start_ts, unit=exp.var("SECONDS"))
4894        )
4895
4896    def arraysize_sql(self, expression: exp.ArraySize) -> str:
4897        dim = expression.expression
4898
4899        # For dialects that don't support the dimension arg, we can safely transpile it's default value (1st dimension)
4900        if dim and self.ARRAY_SIZE_DIM_REQUIRED is None:
4901            if not (dim.is_int and dim.name == "1"):
4902                self.unsupported("Cannot transpile dimension argument for ARRAY_LENGTH")
4903            dim = None
4904
4905        # If dimension is required but not specified, default initialize it
4906        if self.ARRAY_SIZE_DIM_REQUIRED and not dim:
4907            dim = exp.Literal.number(1)
4908
4909        return self.func(self.ARRAY_SIZE_NAME, expression.this, dim)
4910
4911    def attach_sql(self, expression: exp.Attach) -> str:
4912        this = self.sql(expression, "this")
4913        exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
4914        expressions = self.expressions(expression)
4915        expressions = f" ({expressions})" if expressions else ""
4916
4917        return f"ATTACH{exists_sql} {this}{expressions}"
4918
4919    def detach_sql(self, expression: exp.Detach) -> str:
4920        this = self.sql(expression, "this")
4921        # the DATABASE keyword is required if IF EXISTS is set
4922        # without it, DuckDB throws an error: Parser Error: syntax error at or near "exists" (Line Number: 1)
4923        # ref: https://duckdb.org/docs/stable/sql/statements/attach.html#detach-syntax
4924        exists_sql = " DATABASE IF EXISTS" if expression.args.get("exists") else ""
4925
4926        return f"DETACH{exists_sql} {this}"
4927
4928    def attachoption_sql(self, expression: exp.AttachOption) -> str:
4929        this = self.sql(expression, "this")
4930        value = self.sql(expression, "expression")
4931        value = f" {value}" if value else ""
4932        return f"{this}{value}"
4933
4934    def watermarkcolumnconstraint_sql(self, expression: exp.WatermarkColumnConstraint) -> str:
4935        return (
4936            f"WATERMARK FOR {self.sql(expression, 'this')} AS {self.sql(expression, 'expression')}"
4937        )
4938
4939    def encodeproperty_sql(self, expression: exp.EncodeProperty) -> str:
4940        encode = "KEY ENCODE" if expression.args.get("key") else "ENCODE"
4941        encode = f"{encode} {self.sql(expression, 'this')}"
4942
4943        properties = expression.args.get("properties")
4944        if properties:
4945            encode = f"{encode} {self.properties(properties)}"
4946
4947        return encode
4948
4949    def includeproperty_sql(self, expression: exp.IncludeProperty) -> str:
4950        this = self.sql(expression, "this")
4951        include = f"INCLUDE {this}"
4952
4953        column_def = self.sql(expression, "column_def")
4954        if column_def:
4955            include = f"{include} {column_def}"
4956
4957        alias = self.sql(expression, "alias")
4958        if alias:
4959            include = f"{include} AS {alias}"
4960
4961        return include
4962
4963    def xmlelement_sql(self, expression: exp.XMLElement) -> str:
4964        name = f"NAME {self.sql(expression, 'this')}"
4965        return self.func("XMLELEMENT", name, *expression.expressions)
4966
4967    def xmlkeyvalueoption_sql(self, expression: exp.XMLKeyValueOption) -> str:
4968        this = self.sql(expression, "this")
4969        expr = self.sql(expression, "expression")
4970        expr = f"({expr})" if expr else ""
4971        return f"{this}{expr}"
4972
4973    def partitionbyrangeproperty_sql(self, expression: exp.PartitionByRangeProperty) -> str:
4974        partitions = self.expressions(expression, "partition_expressions")
4975        create = self.expressions(expression, "create_expressions")
4976        return f"PARTITION BY RANGE {self.wrap(partitions)} {self.wrap(create)}"
4977
4978    def partitionbyrangepropertydynamic_sql(
4979        self, expression: exp.PartitionByRangePropertyDynamic
4980    ) -> str:
4981        start = self.sql(expression, "start")
4982        end = self.sql(expression, "end")
4983
4984        every = expression.args["every"]
4985        if isinstance(every, exp.Interval) and every.this.is_string:
4986            every.this.replace(exp.Literal.number(every.name))
4987
4988        return f"START {self.wrap(start)} END {self.wrap(end)} EVERY {self.wrap(self.sql(every))}"
4989
4990    def unpivotcolumns_sql(self, expression: exp.UnpivotColumns) -> str:
4991        name = self.sql(expression, "this")
4992        values = self.expressions(expression, flat=True)
4993
4994        return f"NAME {name} VALUE {values}"
4995
4996    def analyzesample_sql(self, expression: exp.AnalyzeSample) -> str:
4997        kind = self.sql(expression, "kind")
4998        sample = self.sql(expression, "sample")
4999        return f"SAMPLE {sample} {kind}"
5000
5001    def analyzestatistics_sql(self, expression: exp.AnalyzeStatistics) -> str:
5002        kind = self.sql(expression, "kind")
5003        option = self.sql(expression, "option")
5004        option = f" {option}" if option else ""
5005        this = self.sql(expression, "this")
5006        this = f" {this}" if this else ""
5007        columns = self.expressions(expression)
5008        columns = f" {columns}" if columns else ""
5009        return f"{kind}{option} STATISTICS{this}{columns}"
5010
5011    def analyzehistogram_sql(self, expression: exp.AnalyzeHistogram) -> str:
5012        this = self.sql(expression, "this")
5013        columns = self.expressions(expression)
5014        inner_expression = self.sql(expression, "expression")
5015        inner_expression = f" {inner_expression}" if inner_expression else ""
5016        update_options = self.sql(expression, "update_options")
5017        update_options = f" {update_options} UPDATE" if update_options else ""
5018        return f"{this} HISTOGRAM ON {columns}{inner_expression}{update_options}"
5019
5020    def analyzedelete_sql(self, expression: exp.AnalyzeDelete) -> str:
5021        kind = self.sql(expression, "kind")
5022        kind = f" {kind}" if kind else ""
5023        return f"DELETE{kind} STATISTICS"
5024
5025    def analyzelistchainedrows_sql(self, expression: exp.AnalyzeListChainedRows) -> str:
5026        inner_expression = self.sql(expression, "expression")
5027        return f"LIST CHAINED ROWS{inner_expression}"
5028
5029    def analyzevalidate_sql(self, expression: exp.AnalyzeValidate) -> str:
5030        kind = self.sql(expression, "kind")
5031        this = self.sql(expression, "this")
5032        this = f" {this}" if this else ""
5033        inner_expression = self.sql(expression, "expression")
5034        return f"VALIDATE {kind}{this}{inner_expression}"
5035
5036    def analyze_sql(self, expression: exp.Analyze) -> str:
5037        options = self.expressions(expression, key="options", sep=" ")
5038        options = f" {options}" if options else ""
5039        kind = self.sql(expression, "kind")
5040        kind = f" {kind}" if kind else ""
5041        this = self.sql(expression, "this")
5042        this = f" {this}" if this else ""
5043        mode = self.sql(expression, "mode")
5044        mode = f" {mode}" if mode else ""
5045        properties = self.sql(expression, "properties")
5046        properties = f" {properties}" if properties else ""
5047        partition = self.sql(expression, "partition")
5048        partition = f" {partition}" if partition else ""
5049        inner_expression = self.sql(expression, "expression")
5050        inner_expression = f" {inner_expression}" if inner_expression else ""
5051        return f"ANALYZE{options}{kind}{this}{partition}{mode}{inner_expression}{properties}"
5052
5053    def xmltable_sql(self, expression: exp.XMLTable) -> str:
5054        this = self.sql(expression, "this")
5055        namespaces = self.expressions(expression, key="namespaces")
5056        namespaces = f"XMLNAMESPACES({namespaces}), " if namespaces else ""
5057        passing = self.expressions(expression, key="passing")
5058        passing = f"{self.sep()}PASSING{self.seg(passing)}" if passing else ""
5059        columns = self.expressions(expression, key="columns")
5060        columns = f"{self.sep()}COLUMNS{self.seg(columns)}" if columns else ""
5061        by_ref = f"{self.sep()}RETURNING SEQUENCE BY REF" if expression.args.get("by_ref") else ""
5062        return f"XMLTABLE({self.sep('')}{self.indent(namespaces + this + passing + by_ref + columns)}{self.seg(')', sep='')}"
5063
5064    def xmlnamespace_sql(self, expression: exp.XMLNamespace) -> str:
5065        this = self.sql(expression, "this")
5066        return this if isinstance(expression.this, exp.Alias) else f"DEFAULT {this}"
5067
5068    def export_sql(self, expression: exp.Export) -> str:
5069        this = self.sql(expression, "this")
5070        connection = self.sql(expression, "connection")
5071        connection = f"WITH CONNECTION {connection} " if connection else ""
5072        options = self.sql(expression, "options")
5073        return f"EXPORT DATA {connection}{options} AS {this}"
5074
5075    def declare_sql(self, expression: exp.Declare) -> str:
5076        return f"DECLARE {self.expressions(expression, flat=True)}"
5077
5078    def declareitem_sql(self, expression: exp.DeclareItem) -> str:
5079        variable = self.sql(expression, "this")
5080        default = self.sql(expression, "default")
5081        default = f" = {default}" if default else ""
5082
5083        kind = self.sql(expression, "kind")
5084        if isinstance(expression.args.get("kind"), exp.Schema):
5085            kind = f"TABLE {kind}"
5086
5087        return f"{variable} AS {kind}{default}"
5088
5089    def recursivewithsearch_sql(self, expression: exp.RecursiveWithSearch) -> str:
5090        kind = self.sql(expression, "kind")
5091        this = self.sql(expression, "this")
5092        set = self.sql(expression, "expression")
5093        using = self.sql(expression, "using")
5094        using = f" USING {using}" if using else ""
5095
5096        kind_sql = kind if kind == "CYCLE" else f"SEARCH {kind} FIRST BY"
5097
5098        return f"{kind_sql} {this} SET {set}{using}"
5099
5100    def parameterizedagg_sql(self, expression: exp.ParameterizedAgg) -> str:
5101        params = self.expressions(expression, key="params", flat=True)
5102        return self.func(expression.name, *expression.expressions) + f"({params})"
5103
5104    def anonymousaggfunc_sql(self, expression: exp.AnonymousAggFunc) -> str:
5105        return self.func(expression.name, *expression.expressions)
5106
5107    def combinedaggfunc_sql(self, expression: exp.CombinedAggFunc) -> str:
5108        return self.anonymousaggfunc_sql(expression)
5109
5110    def combinedparameterizedagg_sql(self, expression: exp.CombinedParameterizedAgg) -> str:
5111        return self.parameterizedagg_sql(expression)
5112
5113    def show_sql(self, expression: exp.Show) -> str:
5114        self.unsupported("Unsupported SHOW statement")
5115        return ""
5116
5117    def get_put_sql(self, expression: exp.Put | exp.Get) -> str:
5118        # Snowflake GET/PUT statements:
5119        #   PUT <file> <internalStage> <properties>
5120        #   GET <internalStage> <file> <properties>
5121        props = expression.args.get("properties")
5122        props_sql = self.properties(props, prefix=" ", sep=" ", wrapped=False) if props else ""
5123        this = self.sql(expression, "this")
5124        target = self.sql(expression, "target")
5125
5126        if isinstance(expression, exp.Put):
5127            return f"PUT {this} {target}{props_sql}"
5128        else:
5129            return f"GET {target} {this}{props_sql}"
5130
5131    def translatecharacters_sql(self, expression: exp.TranslateCharacters):
5132        this = self.sql(expression, "this")
5133        expr = self.sql(expression, "expression")
5134        with_error = " WITH ERROR" if expression.args.get("with_error") else ""
5135        return f"TRANSLATE({this} USING {expr}{with_error})"
5136
5137    def decodecase_sql(self, expression: exp.DecodeCase) -> str:
5138        if self.SUPPORTS_DECODE_CASE:
5139            return self.func("DECODE", *expression.expressions)
5140
5141        expression, *expressions = expression.expressions
5142
5143        ifs = []
5144        for search, result in zip(expressions[::2], expressions[1::2]):
5145            if isinstance(search, exp.Literal):
5146                ifs.append(exp.If(this=expression.eq(search), true=result))
5147            elif isinstance(search, exp.Null):
5148                ifs.append(exp.If(this=expression.is_(exp.Null()), true=result))
5149            else:
5150                if isinstance(search, exp.Binary):
5151                    search = exp.paren(search)
5152
5153                cond = exp.or_(
5154                    expression.eq(search),
5155                    exp.and_(expression.is_(exp.Null()), search.is_(exp.Null()), copy=False),
5156                    copy=False,
5157                )
5158                ifs.append(exp.If(this=cond, true=result))
5159
5160        case = exp.Case(ifs=ifs, default=expressions[-1] if len(expressions) % 2 == 1 else None)
5161        return self.sql(case)
5162
5163    def semanticview_sql(self, expression: exp.SemanticView) -> str:
5164        this = self.sql(expression, "this")
5165        this = self.seg(this, sep="")
5166        dimensions = self.expressions(
5167            expression, "dimensions", dynamic=True, skip_first=True, skip_last=True
5168        )
5169        dimensions = self.seg(f"DIMENSIONS {dimensions}") if dimensions else ""
5170        metrics = self.expressions(
5171            expression, "metrics", dynamic=True, skip_first=True, skip_last=True
5172        )
5173        metrics = self.seg(f"METRICS {metrics}") if metrics else ""
5174        where = self.sql(expression, "where")
5175        where = self.seg(f"WHERE {where}") if where else ""
5176        return f"SEMANTIC_VIEW({self.indent(this + metrics + dimensions + where)}{self.seg(')', sep='')}"
5177
5178    def getextract_sql(self, expression: exp.GetExtract) -> str:
5179        this = expression.this
5180        expr = expression.expression
5181
5182        if not this.type or not expression.type:
5183            from sqlglot.optimizer.annotate_types import annotate_types
5184
5185            this = annotate_types(this, dialect=self.dialect)
5186
5187        if this.is_type(*(exp.DataType.Type.ARRAY, exp.DataType.Type.MAP)):
5188            return self.sql(exp.Bracket(this=this, expressions=[expr]))
5189
5190        return self.sql(exp.JSONExtract(this=this, expression=self.dialect.to_json_path(expr)))
5191
5192    def datefromunixdate_sql(self, expression: exp.DateFromUnixDate) -> str:
5193        return self.sql(
5194            exp.DateAdd(
5195                this=exp.cast(exp.Literal.string("1970-01-01"), exp.DataType.Type.DATE),
5196                expression=expression.this,
5197                unit=exp.var("DAY"),
5198            )
5199        )
5200
5201    def space_sql(self: Generator, expression: exp.Space) -> str:
5202        return self.sql(exp.Repeat(this=exp.Literal.string(" "), times=expression.this))
5203
5204    def buildproperty_sql(self, expression: exp.BuildProperty) -> str:
5205        return f"BUILD {self.sql(expression, 'this')}"
5206
5207    def refreshtriggerproperty_sql(self, expression: exp.RefreshTriggerProperty) -> str:
5208        method = self.sql(expression, "method")
5209        kind = expression.args.get("kind")
5210        if not kind:
5211            return f"REFRESH {method}"
5212
5213        every = self.sql(expression, "every")
5214        unit = self.sql(expression, "unit")
5215        every = f" EVERY {every} {unit}" if every else ""
5216        starts = self.sql(expression, "starts")
5217        starts = f" STARTS {starts}" if starts else ""
5218
5219        return f"REFRESH {method} ON {kind}{every}{starts}"